Search in Polecat, Part 2: Vector search on a computed VECTOR column

· 9 min read · polecat sql-server dotnet critter-stack vector-search embeddings
Search in Polecat, Part 2: Vector search on a computed VECTOR column

Series: Search in Polecat

  1. 1 Search in Polecat, Part 1: Full text search on an index it owns
  2. 2 Search in Polecat, Part 2: Vector search on a computed VECTOR column
  3. 3 Search in Polecat, Part 3: Hybrid search and reciprocal rank fusion
← Previous Next →

Part 1 covered keyword search, which finds the words you typed and only those.

SQL Server 2025 shipped a native VECTOR type and VECTOR_DISTANCE. Polecat 5.28 searches by embedding similarity over a member you declare, which finds the limescale article when you searched for hard water.

Same corpus as Part 1, run against Polecat 5.29.0 and SQL Server 2025 (17.0.4085.5).

Needs SQL Server 2025 or later. The VECTOR type does not exist on Azure SQL Edge or earlier versions, and Polecat names the member and the requirement when the schema is applied.

Part 1’s article gains one member, the vector itself:

/// <summary>The same article, now carrying the vector Polecat searches by.</summary>
public class Article
{
    public Guid Id { get; set; }
    public required string Title { get; set; }
    public required string Body { get; set; }
    public float[]? Embedding { get; set; }
}
opts.Schema.For<Article>().VectorIndex(x => x.Embedding, dimensions: Embeddings.Dimensions);
    foreach (var article in articles)
    {
        article.Embedding = Embeddings.Embed(article.Body);
    }

    await using var session = store.LightweightSession();
    session.Store(articles);
    await session.SaveChangesAsync();
}
// The question is embedded by the same code that embedded the bodies. A vector from one
// model means nothing to an index built by another.
var queryVector = Embeddings.Embed("hard water");

var nearest = await session.VectorSearchWithScoresAsync<Article>(
    x => x.Embedding, queryVector, limit: 4);

VectorSearchWithScoresAsync(x => x.Embedding, queryVector, limit: 4)

rankdistancearticle
10.0194Water hardness and machine lifetime
20.0572Descaling in hard water areas
30.7868Monthly maintenance schedule
41.0000Grinder burr replacement

The nearest article is one keyword search cannot find: it says hardness where the query says hard, and this index does not stem.

Why float[] and not a vector type

Microsoft.Data.SqlClient 7.0 does have SqlVector<T>, but it is an ADO.NET parameter type, not a document-model one. A Polecat document is stored as JSON, so the embedding has to serialise into data alongside every other member, and float[] is what serialises naturally. Nothing on the write path treats it as special.

The native type still does the work, one layer down. Polecat derives a real VECTOR(n) column from that JSON, so VECTOR_DISTANCE compares two genuine vectors: the persisted column, parsed once when the row was written, and the query vector, which Polecat sends as a text literal and casts once per statement.

Polecat never calls a model. It stores the vector you hand it and orders by distance to the vector you search with. Embeddings.Embed above is a deterministic stand-in so these distances do not move between runs.

Any member serialising as an array of numbers works: float[], double[], ReadOnlyMemory<float>, Memory<float>, or a List<T>, IList<T>, IReadOnlyList<T>, ICollection<T>, IReadOnlyCollection<T> or IEnumerable<T> of float or double. Anything else is refused when the store is configured, including ReadOnlyMemory<double>.

Producing the embeddings

Microsoft.Extensions.AI gives every provider one interface, IEmbeddingGenerator<string, Embedding<float>>, so the model is a line of construction and not a shape your storage code knows about.

Locally, through Ollama, with no per-call cost:

/// <summary>
///     A local model through Ollama: nothing leaves the machine and there is no per-call cost.
///     `ollama pull nomic-embed-text` first.
/// </summary>
public static IEmbeddingGenerator<string, Embedding<float>> ViaOllama() =>
    new OllamaApiClient(new Uri("http://localhost:11434"), "nomic-embed-text");

OpenAI, or Azure OpenAI by swapping the client for AzureOpenAIClient:

/// <summary>
///     OpenAI. For Azure OpenAI, swap OpenAIClient for AzureOpenAIClient; everything after it
///     is identical.
/// </summary>
public static IEmbeddingGenerator<string, Embedding<float>> ViaOpenAI(string apiKey) =>
    new OpenAIClient(apiKey)
        .GetEmbeddingClient("text-embedding-3-small")
        .AsIEmbeddingGenerator();

Claude has no embeddings endpoint. Anthropic ships no embedding model and points to Voyage AI, which has no first-party .NET SDK, so that route is an HTTP call behind the same interface.

Either way the call takes a batch:

/// <summary>
///     One call for the whole page, not one per document. Vectors come back in input order.
/// </summary>
public static async Task EmbedAllAsync(
    IEmbeddingGenerator<string, Embedding<float>> model, Article[] articles)
{
    var vectors = await model.GenerateAsync(articles.Select(x => x.Body));

    for (var i = 0; i < articles.Length; i++)
    {
        articles[i].Embedding = vectors[i].Vector.ToArray();
    }
}
  • Embed a page, not a document. Providers bill per call. Vectors return in input order, so pairing is positional.
  • Embed outside the unit of work. Holding a transaction open across a third-party call turns a slow model into database contention.
  • Use the same model on both sides. A query vector from one model against an index built by another returns numbers, not errors, and nothing reports it.
  • Match the declared dimension count, taking it from the model rather than writing a literal twice:
/// <summary>
///     Ask the model how wide its vectors are, rather than hardcoding a number that has to
///     agree with the VectorIndex declaration.
/// </summary>
public static int DimensionsOf(IEmbeddingGenerator<string, Embedding<float>> model) =>
    model.GetService<EmbeddingGeneratorMetadata>()?.DefaultModelDimensions
    ?? throw new InvalidOperationException("This generator does not publish a dimension count.");

text-embedding-3-small is 1536, nomic-embed-text is 768, and the text-embedding-3 models accept a shorter length at generation time. Changing model later means re-embedding the corpus and changing the declared dimensions: a migration, not a config change.

The computed column

That derived column is the whole of the storage design. Declaring a vector adds one thing to the schema, here read back from sys.computed_columns:

ALTER TABLE dbo.pc_doc_article ADD [vec_embedding] AS (CONVERT([sys].[vector](8),json_query([data],'$.embedding'))) PERSISTED;
  ^ the catalog renders the authored CAST(... AS VECTOR(n)) as CONVERT; same column

PERSISTED is what makes it storage rather than an expression: the vector is computed on write and read back like any other column. The search is then one statement: the document’s columns plus VECTOR_DISTANCE('cosine', [vec_embedding], @query), ordered by distance, cut by TOP.

The alternative would be a VECTOR column written directly by the session. Computing it instead pays three times. The write path is untouched. The column cannot drift from the document, where one maintained on the write path goes stale under any writer that is not Polecat. And declaring a vector on a type that already has rows makes all of them searchable after that single ALTER TABLE, which is the work Part 1’s inverted index needed an explicit backfill to do.

One detail: JSON_QUERY, not JSON_VALUE. Every other computed column in Polecat extracts a scalar through JSON_VALUE, which returns NULL for an array whatever its length, and NULL again for any scalar over 4000 characters. A 768-float array runs to about 6,900. JSON_QUERY returns the array intact, which is why a vector column is its own type in Polecat rather than an ordinary computed column with a different SQL type.

Three metrics, all distances

var byMetric = await session.VectorSearchWithScoresAsync<Article>(
    x => x.Embedding, queryVector, limit: 1, distance: metric);

the same search, limit: 1, under each DistanceFunction

metricdistancearticle
Cosine0.01942Water hardness and machine lifetime
L20.19708Water hardness and machine lifetime
InnerProduct-0.98058Water hardness and machine lifetime
DistanceFunctionSQL Server metricUse for
Cosine (default)cosinetext embeddings, trained for it and usually unit length
L2euclideanwhen magnitude carries meaning
InnerProductdotunit vectors, where it equals cosine and is cheaper

The inner product is negative: SQL Server returns the dot product already negated, so all three are distances and smaller is closer. That uniformity lets one ORDER BY serve all of them and a similarity floor be a single comparison.

The one real limit: no approximate index

Polecat creates no index over the vector column. SQL Server will not build a NONCLUSTERED index over one, and CREATE VECTOR INDEX is a DiskANN index sitting behind PREVIEW_FEATURES. Create one and the table becomes read only: the next insert fails with

Msg 42231: Data modification statement failed because table 'v' has a vector index on it.

So this is exact k-nearest-neighbour over a sequential scan. Fine at thousands of rows, fine-to-slow at millions; measure before trusting it on a large corpus. VectorSearchAsync is the seam an approximate index would slot behind, so the API will not move when the engine ships one that permits writes.

The scan pays for itself in one way: the result is a true top-k, with none of the recall caveats an HNSW index has to document.

What it refuses

no vector index on the type
    InvalidOperationException: 'Note' declares no vector index, so there is nothing to search. Declare one with Schema.For<Note>().VectorIndex(x => x.Embedding, dimensions).

a member that is not the declared one
    InvalidOperationException: 'Article.Title' is not a declared vector member. Declared: Embedding.

a query vector of the wrong length
    ArgumentException: The query vector has 3 dimensions but 'Article.Embedding' was declared with 8. (Parameter 'query')

The dimension check earns its keep most often: without it SQL Server rejects every row individually and the search returns nothing.

Next

Vector search is weakest where keyword search is strongest. An embedding model places text by meaning, and a fault code like Part 1’s E-142 has almost none: it is an identifier, not a subject. The same goes for part numbers and surnames. Part 3 fuses the two rankings.

Reference

Series: Search in Polecat

  1. 1 Search in Polecat, Part 1: Full text search on an index it owns
  2. 2 Search in Polecat, Part 2: Vector search on a computed VECTOR column
  3. 3 Search in Polecat, Part 3: Hybrid search and reciprocal rank fusion
← Previous Next →
#polecat #sql-server #dotnet #critter-stack #vector-search #embeddings