VECTOR_SEARCH in SQL Server 2025: Finding Similar Rows With a Vector Index

VECTOR_SEARCH returns the rows whose numbers sit closest to a list of numbers you give it. In SQL Server 2025 it can use a vector index. You can test the whole thing without an AI model, using a few numbers you type by hand.

What a Vector Is

A vector is a list of numbers stored in one column. SQL Server 2025 has a data type for it, written vector(3) for a list of three numbers. Real embeddings from an AI model hold hundreds or thousands of numbers. Three is enough to learn the syntax and to check the answers by eye.

Two vectors are similar when they point the same way. Cosine distance measures the angle between them. A distance near 0 means almost the same direction, and a distance near 1 means unrelated. That is the whole idea behind “find similar rows”.

It Is a Preview Feature

Vector indexes are a preview feature in this release. Preview features sit behind a database scoped setting named PREVIEW_FEATURES, and I switch it on in the test database only. Treat everything here as a test, not a design for production.

The setting matters. With it off, the command that creates the index fails. You will see that failure in a moment.

Six Rows of Hand-Made Numbers

I ran everything on SQL Server 2025, build 17.0.5005.3. The table holds three fruits and three vehicles. I invented the numbers. The first number leans toward fruit, and the third leans toward vehicle.

IF DB_ID(N'SqlVectorSearchDemo') IS NULL CREATE DATABASE SqlVectorSearchDemo;
GO
USE SqlVectorSearchDemo;
GO
CREATE TABLE dbo.Items
(
    ItemID int IDENTITY(1,1) PRIMARY KEY,
    Name nvarchar(50) NOT NULL,
    Kind nvarchar(20) NOT NULL,
    Embedding vector(3) NOT NULL
);
INSERT INTO dbo.Items (Name, Kind, Embedding) VALUES
(N'apple', N'fruit', CAST('[0.9, 0.1, 0.0]' AS vector(3))),
(N'pear', N'fruit', CAST('[0.8, 0.2, 0.1]' AS vector(3))),
(N'banana', N'fruit', CAST('[0.7, 0.3, 0.0]' AS vector(3))),
(N'car', N'vehicle', CAST('[0.0, 0.1, 0.9]' AS vector(3))),
(N'bus', N'vehicle', CAST('[0.1, 0.0, 0.8]' AS vector(3))),
(N'bicycle', N'vehicle', CAST('[0.1, 0.2, 0.7]' AS vector(3)));

Build the Vector Index

First, the failure. The index uses the metric cosine and the type DiskANN. DiskANN is a graph-based method that finds near neighbors without comparing every row. With the preview setting off, the statement below fails, and the second block shows the message.

CREATE VECTOR INDEX ix_Items_Embedding ON dbo.Items (Embedding) WITH (METRIC = 'cosine', TYPE = 'DiskANN');

Msg 343, Level 15, State 2, Line 1
Unknown object type 'VECTOR' used in a CREATE, DROP, or ALTER statement.

Now turn the setting on and run the same statement again. This time the index builds.

ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;
GO
CREATE VECTOR INDEX ix_Items_Embedding ON dbo.Items (Embedding) WITH (METRIC = 'cosine', TYPE = 'DiskANN');

It worked. SQL Server also printed a warning that the join order has been enforced. The index was created and the later searches ran.

Search for Something Like an Apple

Now ask for the three rows closest to a made-up query vector that leans toward fruit. The function reads the table you name, and TOP_N says how many rows to return. It adds a distance column. I sort by it and cast it to four decimals to keep the output short. A smaller distance means a closer row, so the best match comes first.

DECLARE @q vector(3) = CAST('[0.85, 0.15, 0.05]' AS vector(3));
SELECT t.Name, t.Kind, CAST(s.distance AS decimal(6,4)) AS Distance
FROM VECTOR_SEARCH(TABLE = dbo.Items AS t, COLUMN = Embedding, SIMILAR_TO = @q, METRIC = 'cosine', TOP_N = 3) AS s
ORDER BY s.distance;

Apple and pear are close to the query. Banana is a little farther. Now a query that leans toward vehicle. This time I ask for all six rows, so the far ones show too.

DECLARE @q vector(3) = CAST('[0.05, 0.1, 0.85]' AS vector(3));
SELECT t.Name, t.Kind, CAST(s.distance AS decimal(6,4)) AS Distance
FROM VECTOR_SEARCH(TABLE = dbo.Items AS t, COLUMN = Embedding, SIMILAR_TO = @q, METRIC = 'cosine', TOP_N = 6) AS s
ORDER BY s.distance;

The gap is plain. The vehicles sit under 0.02 and the fruits sit near 0.8 or more. Nothing in the table says “apple is a fruit”. The numbers alone carry the meaning.

Exact or Approximate

There are two ways to find the nearest rows. VECTOR_DISTANCE computes the distance for every row, then you sort. That is exact. The index takes a shortcut through a graph and can skip a close row. That is approximate.

On six rows, both agree. This query uses no index and returns the same three rows and the same distances as the first search.

DECLARE @q vector(3) = CAST('[0.85, 0.15, 0.05]' AS vector(3));
SELECT TOP (3) Name, Kind, CAST(VECTOR_DISTANCE('cosine', Embedding, @q) AS decimal(6,4)) AS Distance
FROM dbo.Items
ORDER BY VECTOR_DISTANCE('cosine', Embedding, @q);

A bigger test is more honest. The next script builds 5,000 points from sine and cosine of the row number. It indexes them. Then it compares the top 10 from the index with the top 10 from the exact scan.

CREATE TABLE dbo.Points (PointID int PRIMARY KEY, V vector(3) NOT NULL);
INSERT INTO dbo.Points (PointID, V)
SELECT s.value, CAST(CONCAT('[', SIN(s.value * 0.37), ',', COS(s.value * 0.91), ',', SIN(s.value * 1.7 + 1), ']') AS vector(3))
FROM GENERATE_SERIES(1, 5000) AS s;
CREATE VECTOR INDEX ix_Points_V ON dbo.Points (V) WITH (METRIC = 'cosine', TYPE = 'DiskANN');
GO
DECLARE @q vector(3) = CAST('[0.3, 0.5, 0.8]' AS vector(3));
SELECT t.PointID INTO #approx
FROM VECTOR_SEARCH(TABLE = dbo.Points AS t, COLUMN = V, SIMILAR_TO = @q, METRIC = 'cosine', TOP_N = 10) AS s;
SELECT TOP (10) PointID INTO #exact
FROM dbo.Points
ORDER BY VECTOR_DISTANCE('cosine', V, @q);
SELECT COUNT(*) AS SameRows
FROM #approx AS a
JOIN #exact AS e ON e.PointID = a.PointID;

The first SELECT keeps the 10 ids the index returned. The second keeps the 10 ids of the exact scan. The last one counts the ids found in both lists. All 10 matched. That is one query on easy three-number data, so it proves little about a real model with hundreds of numbers. I did not time either method. In the plan, the search showed an operator named Vector Index Seek, so the index was used.

Use the exact method when the table is small, or when a missed row is not acceptable. Use the index when the table is large and a close answer is enough. The three-number test cannot tell you where your own line sits, so measure on your own data.

The Limits I Hit

The biggest limit is that the table becomes read-only once it has a vector index. Each of these three statements failed in its own batch with the same message.

INSERT INTO dbo.Items (Name, Kind, Embedding) VALUES (N'plum', N'fruit', CAST('[0.8, 0.1, 0.1]' AS vector(3)));
GO
UPDATE dbo.Items SET Name = N'green apple' WHERE ItemID = 1;
GO
DELETE FROM dbo.Items WHERE ItemID = 6;

Msg 42231, Level 16, State 1, Line 1
Data modification statement failed because table 'Items' has a vector index on it.
Msg 42231, Level 16, State 1, Line 1
Data modification statement failed because table 'Items' has a vector index on it.
Msg 42231, Level 16, State 1, Line 1
Data modification statement failed because table 'Items' has a vector index on it.

The metric must also match. I asked for euclidean distance against the cosine index. The search failed with Msg 42227, which says it cannot find a vector index with that metric on the column.

DECLARE @q vector(3) = CAST('[0.05, 0.1, 0.85]' AS vector(3));
SELECT t.Name, CAST(s.distance AS decimal(6,4)) AS Distance
FROM VECTOR_SEARCH(TABLE = dbo.Items AS t, COLUMN = Embedding, SIMILAR_TO = @q, METRIC = 'euclidean', TOP_N = 3) AS s
ORDER BY s.distance;

Msg 42227, Level 16, State 1, Line 2
Cannot find a vector index with metric 'euclidean' on column 'Embedding'.

The last trap is a filter. A WHERE clause runs after the search, not inside it. Ask for the three nearest rows to a vehicle-like vector, then keep only the fruit, and you get nothing. All three nearest rows were vehicles.

DECLARE @q vector(3) = CAST('[0.05, 0.1, 0.85]' AS vector(3));
SELECT t.Name, t.Kind, CAST(s.distance AS decimal(6,4)) AS Distance
FROM VECTOR_SEARCH(TABLE = dbo.Items AS t, COLUMN = Embedding, SIMILAR_TO = @q, METRIC = 'cosine', TOP_N = 3) AS s
WHERE t.Kind = N'fruit'
ORDER BY s.distance;

The query returned zero rows. If you filter by category, ask for more rows than you need, then filter, or plan one index per category.

The Fair Complaint

You could say a six-row table proves nothing, and that a read-only table makes the feature useless. Fair point on both. Six rows cannot show speed, and I did not try to. The read-only rule suits data you load once and search many times. It does not suit a table that changes all day.

Dropping the index frees the table again, and the same insert then works. Without the index, VECTOR_SEARCH has nothing to use and fails with Msg 42227 for the cosine metric. The exact method with VECTOR_DISTANCE still works with no index at all.

DROP INDEX ix_Items_Embedding ON dbo.Items;
GO
INSERT INTO dbo.Items (Name, Kind, Embedding) VALUES (N'plum', N'fruit', CAST('[0.8, 0.1, 0.1]' AS vector(3)));
GO
DECLARE @q vector(3) = CAST('[0.85, 0.15, 0.05]' AS vector(3));
SELECT t.Name, CAST(s.distance AS decimal(6,4)) AS Distance
FROM VECTOR_SEARCH(TABLE = dbo.Items AS t, COLUMN = Embedding, SIMILAR_TO = @q, METRIC = 'cosine', TOP_N = 3) AS s
ORDER BY s.distance;

Msg 42227, Level 16, State 1, Line 2
Cannot find a vector index with metric 'cosine' on column 'Embedding'.

A Short Checklist

• Turn on PREVIEW_FEATURES in a test database first.
• Match the metric in VECTOR_SEARCH to the metric of the index.
• Plan for a read-only table: load first, index second.
• Filter after the search only when you ask for extra rows.
• Compare the index against VECTOR_DISTANCE on your own data before you trust it.

When you finish testing, remove the example database.

USE master;
GO
ALTER DATABASE SqlVectorSearchDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SqlVectorSearchDemo;

VECTOR_SEARCH is not a search by meaning, it is a search by distance.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.