3.4 — Vector Data, Vector Functions, Indexes and ANN vs ENN Search
DP-800 Domain 3 · Skill 3.4
What You Need to Know
SQL Server 2025, Azure SQL Database, Azure SQL Managed Instance and SQL database in Fabric store embeddings in a native vector type and search them with exact (ENN, also called kNN) or approximate (ANN) nearest-neighbor search.
The vector type
CREATE TABLE dbo.ProductVector (
ProductID int PRIMARY KEY,
Embedding vector(1536) NOT NULL -- float32 by default
);
-- vector(1536, float16) halves the storage, with slightly lower precision
- Stored as optimized binary and shown as a JSON array (
'[0.12, -0.03, ...]'). It converts implicitly from JSON arrays and strings. - Dimensions must match the embedding model exactly. Limits: 1,998 for float32 and 3,996 for float16.
- Storage is roughly dimensions × 4 bytes for float32: a 1,536-dimension vector is about 6 KB per row.
Vector functions
| Function | Returns | Use |
|---|---|---|
VECTOR_DISTANCE('cosine' | 'euclidean' | 'dot', v1, v2) |
A distance (lower is closer) | Exact search: compute it for each candidate row and ORDER BY it |
VECTOR_NORMALIZE(v, 'norm2') |
A unit-length vector | Normalize vectors so that dot product and cosine rank the same way |
VECTORPROPERTY(v, 'Dimensions' | 'BaseType') |
Metadata | Validate dimensions and base type |
VECTOR_SEARCH(TABLE=..., COLUMN=..., SIMILAR_TO=..., METRIC=...) |
Rows plus a distance column | Approximate search using a vector index |
Distance metrics
| Metric | Measures | Typical use |
|---|---|---|
| cosine | Angle between vectors (ignores length) | The default for text embeddings |
| dot | (Negative) dot product | Normalized vectors, where it's equivalent to cosine and cheaper |
| euclidean | Straight-line distance | When magnitude matters |
Use the metric the embedding model is designed for, usually cosine. A vector index and the search query must use the same metric.
ENN (exact) vs ANN (approximate)
-- Exact: scan the candidates and compute every distance
SELECT TOP (10) p.ProductID, VECTOR_DISTANCE('cosine', v.Embedding, @q) AS dist
FROM dbo.ProductVector v JOIN dbo.Product p ON p.ProductID = v.ProductID
WHERE p.Category = 'Outdoor'
ORDER BY dist;
-- Approximate: DiskANN index, then VECTOR_SEARCH
CREATE VECTOR INDEX vix_product ON dbo.ProductVector (Embedding)
WITH (METRIC = 'cosine', TYPE = 'DiskANN');
SELECT TOP (10) WITH APPROXIMATE t.ProductID, s.distance
FROM VECTOR_SEARCH(TABLE = dbo.ProductVector AS t, COLUMN = Embedding,
SIMILAR_TO = @q, METRIC = 'cosine') AS s
ORDER BY s.distance;
| ENN (exact, kNN) | ANN (approximate) | |
|---|---|---|
| Accuracy | Perfect: recall = 1 | High, but approximate (recall below 1) |
| Cost | Grows with the number of rows scanned | Sub-linear: navigates a DiskANN graph |
| When | Small candidate sets: about 50,000 vectors or fewer after filters, or when exactness matters | Large collections, latency-sensitive search |
| Needs | Just VECTOR_DISTANCE |
CREATE VECTOR INDEX + VECTOR_SEARCH with SELECT TOP (N) WITH APPROXIMATE |
- The latest vector indexes support full DML (the table stays writable), iterative filtering (
WHEREpredicates are applied during the search), and the optimizer choosing between the index and kNN. - The older syntax with
TOP_NinsideVECTOR_SEARCHis deprecated. Results must be ordered bydistanceascending. - Vector indexes are GA in Azure SQL Database, SQL database in Fabric and Azure SQL Managed Instance (always-up-to-date), and in preview in SQL Server 2025 (
PREVIEW_FEATURES).
Key Concept: Store embeddings in vector(n) with dimensions that match the model. Use ENN (VECTOR_DISTANCE) for small or filtered sets and exact answers, and ANN (DiskANN index + VECTOR_SEARCH ... TOP (N) WITH APPROXIMATE) for scale. Index and query must use the same metric.
Exam Traps
- Creating the vector index with
euclideanand querying withcosine(the index isn't used; it falls back to kNN with a warning) - Using ANN on a 5,000-row candidate set where exact search is cheap and perfect
- Sorting
VECTOR_SEARCHresults bydistance DESC - Mismatched dimensions between the model output and the column
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 14 DP-800 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Vector data type, Microsoft Learn
- Vector search and vector indexes in the SQL Database Engine, Microsoft Learn
- VECTOR_DISTANCE (Transact-SQL), Microsoft Learn
- VECTOR_SEARCH (Transact-SQL), Microsoft Learn
- CREATE VECTOR INDEX (Transact-SQL), Microsoft Learn