Vector search (SQL Server 2025)

In one line: in SQL Server 2025, the exact nearest-neighbour search (VECTOR_DISTANCE with ORDER BY and TOP) reads every row, every time. The approximate one (VECTOR_SEARCH on a vector index) reads far less but misses some true neighbours. On the tested build, the index also needs preview features and makes the table read-only.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/vector-search on GitHub.

What you’ll see

A similarity search that gets slower as the table grows. The exact search read 7,193 pages per query on a 7,171-page table.
Results that differ from the exact answer. VECTOR_SEARCH found 83% of the true top 10 in one run, and 83% to 93% across runs.
Fewer rows than you asked for. VECTOR_SEARCH ... TOP_N = 10 with a WHERE clause returned 0 rows, where the exact query returned 10.
Error 42231 on any insert, update or delete: “Data modification statement failed because table has a vector index on it.”
Error 343 creating the index: “Unknown object type ‘VECTOR'”.

Why it happens

No ordinary index can order rows by their distance to a query vector, so the exact search computes the distance for every row. A DiskANN vector index is a graph of neighbours: the search walks part of it instead, which is fast but approximate. The graph isn’t built identically every time, so recall varies between builds.

VECTOR_SEARCH finds its top N first. A WHERE clause on the result filters those N rows; it doesn’t search further for rows that pass.

On the tested build the vector index is a preview feature: it needs PREVIEW_FEATURES = ON in the database, and it doesn’t support changes to the table.

What to do

Use the exact search for small tables, or after a filter has already narrowed the rows. It’s always right.
Use VECTOR_SEARCH for a large, mostly static set of vectors, where “most of the nearest rows” is good enough.
Over-fetch when you filter: ask for many more rows than you need, filter, then take the top. In the demo, TOP_N = 1000 left 500 rows after the filter.
Plan loads around the index: drop it, load, rebuild. Building it took nearly four times as long as generating the data.

What doesn’t help

Expecting TOP_N plus WHERE to behave like TOP plus WHERE. It doesn’t search past its first N.
Assuming the index is exact. Check recall against the exact search on your own data.

Check your server

-- SQL Server 2025+: vector columns, vector indexes, and whether this
-- database has preview features on.
SELECT OBJECT_SCHEMA_NAME(c.object_id) AS schema_name,
       OBJECT_NAME(c.object_id)        AS table_name,
       c.name                          AS column_name
FROM sys.columns AS c
WHERE TYPE_NAME(c.user_type_id) = 'vector';

SELECT OBJECT_NAME(object_id) AS table_name, name AS index_name, type_desc
FROM sys.indexes
WHERE type_desc LIKE '%VECTOR%';

SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'PREVIEW_FEATURES';

See it happen

The vector-search demo builds 100,000 synthetic 128-dimension vectors in 200 clusters, with a DiskANN index. It runs 20 queries both ways and measures reads and recall, then tests filtering, writes, the index’s size and build time, and preview features. No AI model is needed. On 2022 and earlier it reports SKIPPED.

Measured on SQL Server 2025 RTM-CU9-GDR (17.0.5015.4), Linux container. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.