Launch Deal · 50% off 1st month ·

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 (WHERE predicates are applied during the search), and the optimizer choosing between the index and kNN.
  • The older syntax with TOP_N inside VECTOR_SEARCH is deprecated. Results must be ordered by distance ascending.
  • 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 euclidean and querying with cosine (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_SEARCH results by distance 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.

See plans

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.