Exact (KNN) vs approximate (ANN) vector search and vector indexes in Azure SQL

Exact nearest neighbour (kNN) search computes the distance to every candidate and is always correct; approximate nearest neighbour (ANN) search uses an index to trade a little recall for a large gain in speed. The engine’s ANN index is a DiskANN graph.

When to use each

The table may contain far more than 50,000 rows as long as relational filters reduce the candidates to that range before the distance calculation.

CREATE VECTOR INDEX

CREATE VECTOR INDEX vix_ProductEmbeddings
ON dbo.ProductEmbeddings (embedding)
WITH (METRIC = 'cosine', TYPE = 'DiskANN', MAXDOP = 8);

Requirements and limits:

• The object must be a base table (not a view or temporary table) and the table must have a clustered primary key on an int column.
• At least 100 rows with non-NULL vectors must exist, otherwise creation fails with error 42266.
• Creating the index needs ALTER on the table. In SQL Server 2025, enable PREVIEW_FEATURES first.
• Vector indexes can’t be partitioned, aren’t replicated to subscribers, and tables with one can’t be truncated (drop the index, truncate, reload at least 100 rows, re-create).
• Vector indexes can’t be deployed with a DACPAC; with DacFx 170.5.96 or later BACPAC import re-creates them after the data loads. A schema-only DACPAC deployment can silently skip the index if the target has fewer than 100 qualifying rows, so check sys.vector_indexes and create it in a post-deployment script if needed.
• The index is used only when the query’s column and metric match it; otherwise VECTOR_SEARCH raises a warning and falls back to kNN.

Earlier versus latest index versions

The latest version is available in Azure SQL Database, SQL database in Fabric and Managed Instance with the Always-up-to-date policy. Newly created indexes there use it automatically.

Common trap: Expecting VECTOR_DISTANCE in ORDER BY to use a new vector index - VECTOR_DISTANCE is always exact and never uses a vector index; only VECTOR_SEARCH performs index-backed ANN search.

Common trap: Assuming a table becomes read-only and results are post-filtered once a vector index exists - that was the earlier index version; latest-version indexes in Azure SQL Database and Fabric support full DML with real-time maintenance and iterative filtering, while vector indexes remain preview (with PREVIEW_FEATURES) in SQL Server 2025.

Get the whole book

This note is one section of Ultra Transcenders DP-800: Developing AI-Enabled Database Solutions, an independent study guide that explains every topic the exam covers by technology, with comparison tables, diagrams and the common traps, plus a glossary linked to Microsoft Learn.

About the book · DP-800 terms in the glossary · All DP-800 study notes