Vector indexes and approximate search

Exact search was fast enough for the catalogue, but Nikolai asked Hana to plan for the next request he could already see coming: the website team wanted semantic search over 2.4 million customer reviews and questions. At that size, computing the distance to every vector for every query is too slow and too expensive. The answer is an approximate nearest-neighbour index, which finds almost all of the closest vectors while examining only a small fraction of them.

Approximate nearest neighbours

Approximate nearest-neighbour search, ANN, trades a little accuracy for a great deal of speed. Instead of comparing the query with every vector, it follows a precomputed structure that leads quickly towards the region where the nearest vectors lie. The result is usually the same as exact search, but not always: occasionally one of the true ten nearest is missed and the eleventh is returned instead. The share of true nearest neighbours that the approximate search returns is called recall. A recall of 0.97 at ten means that, on average, 9.7 of the true ten nearest neighbours are found.

SQL Server 2025 and Azure SQL use DiskANN, an algorithm developed at Microsoft Research. DiskANN builds a graph in which each vector is a node linked to a limited number of neighbours, chosen so that following links from any starting point moves quickly towards any region of the space. A search starts at an entry point, repeatedly moves to the neighbour closest to the query, keeps a short list of the best candidates seen, and stops when no neighbour is closer. Because the graph is designed to work well when most of it is on disk rather than in memory, it suits a database engine, which already manages pages between storage and the buffer pool.

Creating a vector index

A vector index is created with CREATE VECTOR INDEX on a vector column of a base table, with a metric, a type and optionally a maximum degree of parallelism for the build. The metric must be the one queries will use; an index built for cosine is not used by a query asking for Euclidean distance. DiskANN is the only type and the default. The table needs a clustered primary key, and at least 100 rows with non-null vectors must exist when the index is created; with fewer, creation fails. Creating it needs ALTER on the table.

CREATE VECTOR INDEX vix_ReviewEmbedding_cosine
ON web.ReviewEmbedding (Embedding)
WITH (METRIC = 'cosine', TYPE = 'DiskANN', MAXDOP = 4);

-- Which version is it, and how is maintenance doing?
SELECT i.name, JSON_VALUE(v.build_parameters, '$.Version') AS index_version
FROM sys.vector_indexes AS v
JOIN sys.indexes AS i ON i.object_id = v.object_id AND i.index_id = v.index_id;

SELECT * FROM sys.dm_db_vector_indexes;

Availability needs care, because the documentation was moving quickly in 2026. The VECTOR_SEARCH reference lists the feature as generally available in Azure SQL Database, SQL database in Fabric and Managed Instance with the Always-up-to-date policy, while the CREATE VECTOR INDEX reference still describes vector indexes as in preview in Azure SQL Database and Fabric, with rollout varying by region and by index version. In SQL Server 2025, both are preview features that need the PREVIEW_FEATURES database scoped configuration. Hana's note for the pilot reads: confirm the status in the region before go-live, and treat the index as a preview feature until the reference pages agree.

Querying with VECTOR_SEARCH

VECTOR_SEARCH is a table-valued function that takes the table, the vector column, the query vector and the metric, and returns the table's columns plus a distance column. With the latest index version, the number of results is set by SELECT TOP (N) WITH APPROXIMATE and the query must order by distance ascending; ordering by anything else, or descending, is not allowed in that query, though an outer query can re-sort.

DECLARE @q vector(1536) = AI_GENERATE_EMBEDDINGS(N'does the cordless drill battery fit the old charger' USE MODEL CatalogEmbedder);

SELECT TOP (10) WITH APPROXIMATE
       r.ReviewId, r.ProductId, r.ReviewText, s.distance
FROM VECTOR_SEARCH(
        TABLE      = web.ReviewEmbedding AS r,
        COLUMN     = Embedding,
        SIMILAR_TO = @q,
        METRIC     = 'cosine') AS s
WHERE r.Language = 'en' AND r.IsPublished = 1
ORDER BY s.distance;

Filtering changed between index versions, and it matters. Earlier versions searched first and filtered afterwards, so a query for the ten nearest English published reviews might find ten nearest reviews, discard the six that were unpublished or in French, and return only four. Those versions also used a TOP_N argument that developers inflated to compensate. The latest version applies predicates iteratively during the search and keeps going until it has N rows that satisfy them. TOP_N is deprecated and raises an error with a latest-version index. Ordinary B-tree indexes on filter columns, such as Language and IsPublished, help iterative filtering.

The optimizer chooses between the index and an exact scan based on cost and how selective the filter is. A filter that leaves 200 rows is cheaper to answer exactly, and the engine may do that. The FORCE_ANN_ONLY table hint forces the index, which is occasionally useful for testing recall; in normal use, letting the optimizer choose is better. If the metric in VECTOR_SEARCH does not match any index on the column, the function warns and falls back to exact search.

Limits to plan around

Vector indexes have operational limits that Elena listed before agreeing to the design. They cannot be partitioned. They are not replicated to transactional replication subscribers. TRUNCATE TABLE is blocked on a table with a vector index: drop the index, truncate, reload at least 100 rows and re-create it. And databases with vector indexes cannot be exported to a BACPAC or deployed in a DACPAC with the index, so the Import/Export service fails; the workaround is to drop the indexes before export and re-create them after import. Elena added a step to the deployment pipeline that scripts vector indexes separately from the rest of the schema.

Two operational habits keep recall high. First, after replacing most of the vectors, for example after re-embedding everything with a new model, drop and re-create the index, because a graph tuned to the old distribution loses recall when nearly every node changes. Second, remove duplicate embeddings: the review data had thousands of identical one-word reviews such as Great, whose identical vectors crowd the graph and fill result lists with copies. Hana stored each distinct review text once and linked duplicates to it.

Measuring recall

Because approximate search can miss results, it must be measured. The method is simple: run a set of test queries both ways, exactly with VECTOR_DISTANCE and approximately with VECTOR_SEARCH, and count how many of the exact top ten the approximate search also returned. Hana ran 200 sample queries against a test copy of the reviews.

# For each test query: the exact top-10 review IDs and the approximate top-10 review IDs
tests = {
    "battery fits old charger": ([11, 52, 87, 90, 113, 140, 151, 162, 170, 188],
                                 [11, 52, 87, 90, 113, 140, 151, 162, 170, 203]),
    "paint smell in nursery":   ([4, 9, 22, 31, 47, 58, 66, 71, 80, 95],
                                 [4, 9, 22, 31, 47, 58, 66, 71, 80, 95]),
    "deck stain peeling":       ([7, 18, 26, 34, 41, 59, 63, 77, 84, 99],
                                 [7, 18, 26, 34, 41, 59, 63, 84, 99, 120]),
}
recalls = []
for q, (exact, approx) in tests.items():
    r = len(set(exact) & set(approx)) / len(exact)
    recalls.append(r)
    print(f"{q:26s} recall@10 = {r:.1f}")
print("average recall@10:", round(sum(recalls) / len(recalls), 3))

battery fits old charger   recall@10 = 0.9
paint smell in nursery     recall@10 = 1.0
deck stain peeling         recall@10 = 0.9
average recall@10: 0.933

Across all 200 queries, average recall at ten was 0.97, and the median query time fell from about 1.9 seconds of exact search to 35 milliseconds. Nikolai's question settled whether 0.97 was enough: the people reading these results are website shoppers scanning a list, and missing the tenth-best review out of thousands does not hurt them. For a compliance search, where every match must be found, exact search over a filtered set would be the right choice despite the cost.

When not to add an index

Indexes are not free, and a vector index less so than most. Building one over 2.4 million vectors took about 40 minutes on the test database at MAXDOP 4, and the index structures added roughly as much storage again as the vectors themselves. Every insert and update also maintains the index in the background, which costs CPU. For the 18,400 products, none of that buys anything: exact search already meets the time target. Elena's rule is to measure exact search first on realistic data and add a vector index only when a measured query misses its target, and then to measure recall and the maintenance cost before and after.

The index also changes which features are available, as the limits above show. A table that must be truncated nightly, partitioned by month, replicated to a reporting subscriber or exported to a BACPAC for a vendor has a good reason not to carry a vector index, or to keep the vectors in a separate table that can.

Recall is not fixed for an index; it depends on the data and the queries. A new product range, a new language in the reviews or a change in how people phrase questions can all shift it. The team therefore kept the 200 test queries as a permanent recall check, run monthly by an elastic job that computes both the exact and the approximate top ten and records the average recall. If the figure drops below 0.95, the job raises an alert, and the first response is to rebuild the index, which restores recall when the data distribution has drifted. The same job records query times for both methods, so the case for keeping the index is re-made with current numbers rather than remembered from the day it was built.