vector-search-azure-sql

Vector storage and search on Azure SQL Database

The vector type behaves like no other column type in this engine, and the query that looks like vector search is not the query that uses the vector index.

Syntax and status re-checked 2026-09-03 against Microsoft Learn's vector data type, VECTOR_SEARCH and CREATE VECTOR INDEX pages. Every error number and plan below was measured 2026-08-28 against a live Azure SQL Database (SERVERPROPERTY('EngineEdition') returns 5, General Purpose serverless, compatibility level 170) and again against the local Azure SQL Database container, which reports the same edition. They agreed on every error number below.

Build the fixture and the index first

Do not read about the index. Build one. It is the only way to learn whether the preview has reached your engine.

CREATE TABLE dbo.chunks (
    id        int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    body      nvarchar(400) NOT NULL,
    category  nvarchar(40)  NOT NULL,
    embedding vector(5)     NULL
);
GO
INSERT INTO dbo.chunks (body, category, embedding)
SELECT CONCAT(N'chunk ', value), N'general',
       CAST(JSON_ARRAY(CAST(value * 0.01 AS float), CAST(value * 0.02 AS float),
                       CAST(value * 0.03 AS float), CAST(value * 0.04 AS float),
                       CAST(value * 0.05 AS float)) AS vector(5))
FROM GENERATE_SERIES(1, 150);
GO
CREATE NONCLUSTERED INDEX ix_chunks_category ON dbo.chunks (category);
GO
DECLARE @t0 datetime2(3) = SYSUTCDATETIME();
CREATE VECTOR INDEX vi_chunks ON dbo.chunks (embedding)
    WITH (METRIC = 'cosine', TYPE = 'diskann');
SELECT DATEDIFF(millisecond, @t0, SYSUTCDATETIME()) AS build_ms;

Measured over 140 rows: 145 ms on the local container, 276 ms in the cloud. If a plan, a document or a model says this statement is not yet buildable, run it before believing it. You turn nothing on first. Compatibility level is not the gate: column, insert, VECTOR_DISTANCE and approximate search over an existing index all worked at level 150. PREVIEW_FEATURES is not the gate either:

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

Do not read a decision out of that number. On the container it is not deterministic: six freshly created databases on one engine read 0, 1, 0, 1, 1, 1 on 2026-09-03, while model and master both read 0. Learn documents OFF as the default, scopes the requirement to the boxed engine, and its own CREATE VECTOR INDEX example calls the setting "not needed for Azure SQL Database". What matters is the statement above: the index builds and you set nothing. If a document tells you to turn this on first, it is wrong whatever your database happens to report. The type, VECTOR_DISTANCE, VECTOR_NORM, VECTOR_NORMALIZE and VECTORPROPERTY are generally available; CREATE VECTOR INDEX, VECTOR_SEARCH and TOP (N) WITH APPROXIMATE are preview and roll out by region, so a design that only performs with the index carries preview risk.

The correction: which query shape reaches the index

An agent asked for fast similarity search writes this. It is wrong and nothing reports it:

-- Correct results. Never uses a vector index. Computes a distance for EVERY row.
DECLARE @q vector(5) = '[0.3,0.3,0.3,0.3,0.3]';
SELECT TOP (10) id, body, VECTOR_DISTANCE('cosine', embedding, @q) AS distance
FROM dbo.chunks
ORDER BY VECTOR_DISTANCE('cosine', embedding, @q);

Measured plan with a current DiskANN index: Clustered Index Scan, then Compute Scalar evaluating vector_distance per row, then Sort. The index is not in the plan. Learn states it outright: vector distance is always exact and doesn't use any vector index, even if available. The shape that does reach the index is a different statement, not a tuning option:

-- Uses the vector index. Vector Index Seek in the measured plan.
DECLARE @q vector(5) = '[0.3,0.3,0.3,0.3,0.3]';
SELECT TOP (10) WITH APPROXIMATE t.id, t.body, r.distance
FROM VECTOR_SEARCH(TABLE = dbo.chunks AS t, COLUMN = embedding,
                   SIMILAR_TO = @q, METRIC = 'cosine') AS r
WHERE t.category = N'general'
ORDER BY r.distance;

Four shapes, measured on the same table and index:

Query	Measured plan
`ORDER BY VECTOR_DISTANCE(...)` with `TOP`	Clustered Index Scan, exact, full scan
`VECTOR_SEARCH` with `TOP` and no `WITH APPROXIMATE`	Clustered Index Scan, exact kNN
`VECTOR_SEARCH` with `TOP (N) WITH APPROXIMATE`	Vector Index Seek, approximate
`TOP (N) WITH APPROXIMATE` on a table with no vector index	Clustered Index Scan, no error

Every wrong shape returns correct rows. That is the whole hazard. A pipeline tested on a few thousand rows passes, ships, then computes one distance per row per query forever, so the defect shows up as latency and cost, not as a failure anyone can point at. Learn says a warning is raised when no compatible index is found; nothing surfaced here.

Turn the silent fallback into a failure. WITH (FORCE_ANN_ONLY) goes on the VECTOR_SEARCH alias and needs an index and WITH APPROXIMATE present:

DECLARE @q vector(5) = '[0.3,0.3,0.3,0.3,0.3]';
SELECT TOP (10) WITH APPROXIMATE t.id, r.distance
FROM VECTOR_SEARCH(TABLE = dbo.chunks AS t, COLUMN = embedding,
                   SIMILAR_TO = @q, METRIC = 'cosine') AS r WITH (FORCE_ANN_ONLY)
ORDER BY r.distance;

With no usable index that fails with Msg 42227 instead of quietly scanning. Use it in a test, not in production.

The older syntax an agent reaches for first

VECTOR_SEARCH(..., TOP_N = 10) is what most published examples still show. Against a current index it fails with Msg 42274. Match on that number, not the text: Learn prints "version 3 index" where the engine measured here printed "newer index version". TOP_N belongs to an earlier index format that cannot be upgraded in place, so move the count to SELECT TOP (N) WITH APPROXIMATE.

WITH APPROXIMATE is also not a general modifier. Without a VECTOR_SEARCH it fails with Msg 42248, and its ORDER BY is fixed: the distance column, ascending, nothing else, or Msg 42271. Learn's own end to end example on the CREATE VECTOR INDEX page ends ORDER BY s.distance, t.title, which its own VECTOR_SEARCH page says is Msg 42271. Copy the rule, not the example. GROUP BY, window functions, DISTINCT, set operators, extra sort keys and CROSS APPLY all need the approximate search in a subquery and the outer query doing the rest.

What VECTOR_DISTANCE returns, and the sign trap

Three metrics exist and nothing else does: 'cosine', 'euclidean', 'dot'. Anything else is Msg 42201. The metric may be a variable and is case insensitive.

DECLARE @q vector(3) = '[1,2,3]';
SELECT v.label,
       VECTOR_DISTANCE('cosine',    CAST(v.txt AS vector(3)), @q) AS cosine,
       VECTOR_DISTANCE('euclidean', CAST(v.txt AS vector(3)), @q) AS euclidean,
       VECTOR_DISTANCE('dot',       CAST(v.txt AS vector(3)), @q) AS dot
FROM (VALUES ('identical', '[1,2,3]'), ('near', '[1,2,4]'), ('far', '[4,5,6]')) AS v(label, txt);

Row	`cosine`	`euclidean`	`dot`
`[1,2,3]`, identical	5.96e-08	0	-14
`[1,2,4]`	0.0085	1	-17
`[4,5,6]`	0.0254	5.196	-32

It returns a distance: smaller is closer, always, for all three.

Cosine returns 1 minus the similarity, on a range Learn gives as 0 to 2. An identical vector scores near zero, so a threshold copied from a similarity based store inverts.
dot returns the negative inner product. The identical row scores -14, not 14. A query that sorts dot descending, or keeps the largest score, returns the least similar rows, with no error and a full result set, and grounds its answer on the worst matches.
Both arguments must share a base type. float32 against float16 is Msg 42243.

The type, in one place

DECLARE @v vector(3) = '[1,2,3]';
SELECT VECTORPROPERTY(@v, 'Dimensions') AS dims,       -- 3
       VECTORPROPERTY(@v, 'BaseType')   AS base_type,  -- float32
       DATALENGTH(@v)                   AS bytes,      -- 20
       CAST(@v AS nvarchar(max))        AS round_trip;

Dimensions are 1 to 1998, which Learn states and the engine enforces: vector(1999) is Msg 2717, vector(0) is Msg 1001. A populated vector(1998) has DATALENGTH 8000. The declared dimension cannot be changed later, even on an empty table: ALTER COLUMN is Msg 42204, so it is drop and recreate. A 3072 dimension model does not fit, and that fix belongs at embedding time.
Base type is float32 unless float16 is stated. Half precision is preview and is a surface Learn does gate on PREVIEW_FEATURES, it crosses TDS as a JSON string rather than in binary, and it cannot be compared with a float32 vector.
It is written and read as a JSON array, converting implicitly from varchar, nvarchar and json. The dimension in CAST must be a literal: CAST(@s AS vector(@d)) is Msg 102, so a helper taking the dimension as a parameter needs dynamic SQL.
Clients that predate the protocol change see nvarchar(max), because Learn records that sp_describe_first_result_set misreports the type, which is why an ORM maps it to a string. Native handling needs Microsoft.Data.SqlClient 6.1.0 or the JDBC driver 13.1.0.

The restriction list

No model infers this: a vector column looks like a column. Every row was measured.

Refused in a table definition

Attempt	Error
`PRIMARY KEY` or `UNIQUE` on a vector column	`Msg 1919`
`FOREIGN KEY` referencing one	`Msg 1776`, no candidate key to reference
`CHECK` constraint	`Msg 1760`
`DEFAULT` constraint	`Msg 1752`
`PERSISTED` computed column over a vector function	`Msg 4936`, non-deterministic
Partition function or partition key	`Msg 7704`
Alias type with `CREATE TYPE`	`Msg 42212`
Assignment to `sql_variant`	`Msg 206`
Memory optimized table	Not supported
`ALTER COLUMN` to a different dimension	`Msg 42204`

Allowed, though widely assumed otherwise: SPARSE, a non-persisted computed column, an INCLUDE column on a nonclustered index, a system versioned temporal table, a table carrying a clustered columnstore index, SELECT ... INTO, ALTER COLUMN from nvarchar to vector, CASE, ISNULL, UNION ALL and FOR JSON. Also parameters, return types and table variables.

Refused in a query

Attempt	Error
`ORDER BY` or `GROUP BY` the column itself	`Msg 42213`, cannot be compared or sorted
`SELECT DISTINCT`	`Msg 421`
`=`, `<`, `>`, `IN`, a join predicate, `MAX`, `MIN`, `COUNT(DISTINCT ...)`	`Msg 8117`
`UNION`, `INTERSECT`, `EXCEPT`	`Msg 5335`. `UNION ALL` is fine
`CREATE STATISTICS` on the column, or any b-tree or columnstore key	`Msg 1978`
`CAST` to `varbinary`	`Msg 529`
Inserting a JSON array of the wrong length	`Msg 42204`
Inserting a JSON array containing a string	`Msg 13670`

IS NULL and IS NOT NULL are the only comparisons that work. With no equality, deduplicating rows by their embedding is not a query you can write. Deduplicate on a hash of the source text, which Learn wants anyway: duplicates degrade recall. Always Encrypted is unsupported, and sp_verify_database_ledger errors on a database holding a vector column.

What the index requires, and what it costs

The table needs a clustered index. Without one, Msg 42254. Learn says a primary key clustered index; clustered primary keys on int, bigint, nvarchar and a composite all built here, so Msg 42217's narrower wording is stale, not the rule. Re-measured on a real logical server 2026-09-08: bigint and a composite (int, int) both built, a heap was refused with Msg 42254 and not 42217, and no input produced Msg 42217 at all.
At least 100 rows with a non-null vector must already exist, or Msg 42266. A fresh database has no index until it has content, and every query is silently exact until then. The fixture inserts 150 for headroom.
One vector index per column. A second, even with a different metric, is Msg 42230. The metric is fixed at build time, and asking VECTOR_SEARCH for a different one falls back silently. Measured 2026-09-06 against a cosine index on 150 rows: METRIC = 'euclidean' and METRIC = 'dot' both returned a full ordered result set, no error and no warning, computed exactly. This skill said Msg 42227 here until that measurement, and the claim was backwards in the one direction that matters, because a reader who believes the engine will stop them has no reason to check. Msg 42227 is raised only by WITH (FORCE_ANN_ONLY), which is why that hint belongs in the test that proves the index is being read.
The local Azure SQL Database container builds it too. Validate the production query plan and security behavior against Azure SQL Database before relying on local parity.

Operation	Result
`TRUNCATE TABLE`	`Msg 42232`. Drop the index, truncate, reload 100 rows, recreate
`ALTER INDEX ... REBUILD` or `DISABLE`	`Msg 42250`. There is no rebuild
`ALTER TABLE ... DROP COLUMN` on the indexed column	`Msg 5074`
Importing a dacpac or bacpac	Fails. The export is fine. Drop the indexes before export, recreate after the load
Partitioning the table	Not supported

This skill used to report that import row as unsettled and it is settled. Learn states it: the import creates schema objects before it loads data, so the index is built against an empty table and trips the 100 row minimum. sqlpackage-import-export measured the round trip on 2026-08-31 and agreed with Learn. Open it before planning either half, because it owns the sequence.

INSERT, UPDATE, DELETE and MERGE are all supported on a current index and changes are searchable after commit, which is new: earlier index versions made the table read only. Watch sys.dm_db_vector_indexes for background maintenance, and drop and recreate the index after replacing most of the embeddings: the graph was built for the old distribution and recall degrades with no error. Filter predicates apply during the search, so a filtered top ten returns ten rows when ten qualify. Give the filter columns a nonclustered index, as the fixture does.

Check it worked

Three checks. The middle one settles what this skill exists for.

-- 1. The index exists and is the current format. index_version must be 3.
SELECT i.name AS index_name, OBJECT_NAME(v.object_id) AS table_name,
       JSON_VALUE(v.build_parameters, '$.Version') AS index_version
FROM sys.vector_indexes AS v
JOIN sys.indexes AS i ON v.object_id = i.object_id AND v.index_id = i.index_id;

-- 2. The query reaches it. Read the plan and look for Vector Index Seek.
SET STATISTICS XML ON;
DECLARE @q vector(5) = '[0.3,0.3,0.3,0.3,0.3]';
SELECT TOP (10) WITH APPROXIMATE t.id, r.distance
FROM VECTOR_SEARCH(TABLE = dbo.chunks AS t, COLUMN = embedding,
                   SIMILAR_TO = @q, METRIC = 'cosine') AS r
ORDER BY r.distance;
SET STATISTICS XML OFF;

-- 3. Ranking is the right way up: the nearest row has the SMALLEST distance.
DECLARE @p vector(5) = '[0.3,0.3,0.3,0.3,0.3]';
SELECT MIN(VECTOR_DISTANCE('cosine', embedding, @p)) AS nearest,
       MAX(VECTOR_DISTANCE('cosine', embedding, @p)) AS furthest
FROM dbo.chunks;

Run them non-interactively with the password in SQLCMDPASSWORD, keeping the output:

sqlcmd -S <server-name>.database.windows.net,1433 -d <database> -U <user> -C \
  -i check-vector-index.sql -o check-vector-index.out

Expected: check 1 returns one row per vector index with index_version 3; an empty result set means there is no index and every similarity query is scanning. Check 2's plan contains Vector Index Seek; a plan holding only Clustered Index Scan and Compute Scalar is the defect, whatever the rows look like. Check 3 returns nearest below furthest, both non-negative. sqlcmd exiting 0 says nothing about the three, so read the output file.

Do not

Do not conclude the index is in use because results look right. Read the plan, or assert with FORCE_ANN_ONLY. WITH APPROXIMATE is not a droppable hint: it changes the algorithm.
Do not sort a dot distance descending, and do not present a cosine distance as a similarity.
Do not declare vector(3072). The ceiling is 1998, and the fix belongs at embedding time.
Do not deduplicate on the embedding. There is no equality operator to do it with.
Do not raise the compatibility level or set PREVIEW_FEATURES = ON. Neither is the gate, and the container's 1 is not the cloud's value.
Do not report that the index cannot be built on the local container. It builds there.
Do not build the ingest, chunking and grounding pipeline here. That is rag-on-azure-sql.

References

references/vector-restrictions-and-query-plans.md when a claim here disagrees with what you are seeing, which on a preview surface happens: it holds the raw statements behind every table above, the four plans and the reproduction steps.
Read vector data type before choosing a dimension count or a driver, and CREATE VECTOR INDEX before creating an index in production, for preview status and the migration off the earlier format.
Read VECTOR_SEARCH when a query will not compile alongside WITH APPROXIMATE, for the subquery patterns, and Feature availability by region before promising the preview in a region you have not built one in.