Vector Search in SQL Server 2025 With VECTOR_DISTANCE

Similarity searches become easier to manage when the numbers stay beside the rows they describe. SQL Server 2025 adds native vector storage and VECTOR_DISTANCE for ranking those rows by distance.

Keep the Example Deliberately Small

A vector is an ordered set of numbers. A text embedding uses those numbers to represent features learned by a model. Real embeddings come from a model and commonly contain hundreds of dimensions. The hand-written values here demonstrate distance behavior only. They are not embeddings from real text or a real model.

I start with a tiny example before discussing search quality. It makes the difference between direction and magnitude easier to see. The labels below describe our chosen geometric inputs. They do not claim the database understands those words. Naming a row Similar does not persuade mathematics.

Run this script on SQL Server 2025 in one SSMS session. VECTOR(3) stores three single-precision values per vector. The dimension count is part of the type. The stored vectors and search vector must have matching dimensions for the distance operation.

CREATE TABLE #VectorRows
(
    ItemID int NOT NULL PRIMARY KEY,
    ItemLabel nvarchar(40) NOT NULL,
    CategoryID int NOT NULL,
    Coordinates vector(3) NOT NULL
);
INSERT #VectorRows (ItemID, ItemLabel, CategoryID, Coordinates)
VALUES
    (1, N'Same direction', 1, '[1,0,0]'),
    (2, N'Longer same direction', 1, '[4,0,0]'),
    (3, N'Slightly rotated', 1, '[0.9,0.1,0]'),
    (4, N'Perpendicular', 2, '[0,1,0]'),
    (5, N'Opposite direction', 2, '[-1,0,0]');
SELECT ItemID, ItemLabel,
       CAST(Coordinates AS nvarchar(max)) AS VectorText
FROM #VectorRows
ORDER BY ItemID;

Rank by Direction With VECTOR_DISTANCE and Cosine

Cosine distance compares direction. For nonzero vectors, scaling both coordinates along the same direction does not change that direction. Smaller distances indicate closer directions. The documented range is zero through two, with opposing directions at the far end. Do not treat that value as a calibrated probability.

DECLARE @SearchVector vector(3) = '[1,0,0]';
SELECT TOP (3) ItemID, ItemLabel,
       VECTOR_DISTANCE('cosine', @SearchVector, Coordinates) AS DistanceValue
FROM #VectorRows
ORDER BY DistanceValue, ItemID;

TOP limits the returned rows after ordering by calculated distance. ItemID provides a deterministic tie breaker for the chosen inputs. Without it, tied rows can change positions between executions. That matters when an application displays only a small result page or records the first recommendation.

Compare Magnitude With Euclidean Distance

Euclidean distance measures straight-line separation in the coordinate space. It responds to magnitude as well as direction. Two vectors pointing the same way can therefore have different distances from the search vector. The metric choice should follow the embedding model's intended comparison method.

DECLARE @SearchVector vector(3) = '[1,0,0]';
SELECT ItemID, ItemLabel,
       VECTOR_DISTANCE('cosine', @SearchVector, Coordinates) AS CosineDistance,
       VECTOR_DISTANCE('euclidean', @SearchVector, Coordinates) AS EuclideanDistance
FROM #VectorRows
ORDER BY EuclideanDistance, ItemID;

Compare the ordering and values produced by the two columns. Here the longer same-direction row ties the exact match under cosine and drops to last place under euclidean. The farther same-direction input exposes the distinction without needing a large dataset. Avoid transferring a threshold from one metric to another. A cosine threshold and a euclidean threshold have different meanings and scales.

Filter Eligible Rows Before VECTOR_DISTANCE Ranks Them

Similarity belongs inside the application's eligibility rules. Exclude inaccessible, inactive, or wrong-category rows before selecting the nearest candidates. The following example applies a category condition to the exact search. The distance is evaluated for eligible rows, and TOP selects from that eligible set.

DECLARE @SearchVector vector(3) = '[1,0,0]';
SELECT TOP (2) ItemID, ItemLabel,
       VECTOR_DISTANCE('cosine', @SearchVector, Coordinates) AS DistanceValue
FROM #VectorRows
WHERE CategoryID = 1
ORDER BY DistanceValue, ItemID;

A relational index can help narrow that category search when the workload supports it. A conventional B-tree index does not index the vector's geometric distance itself. Keep those two access-path questions separate. The best vector candidate from the entire table is irrelevant if the reader cannot access that row.

Validate the Embedding Contract

Store the model identity and generation version alongside real vectors. A dimension count alone does not prove that two embeddings belong to the same coordinate space. Different models can produce arrays of equal length with incompatible meanings. Compare vectors generated through the same defined process.

Define what happens when source text changes. The row and its embedding need a reliable refresh relationship. A current title beside an obsolete embedding produces plausible but misleading search results. Track generation status so failed refreshes are visible. Decide whether stale rows remain searchable or require exclusion.

Reject unsuitable input before it reaches the similarity query. Check dimension count, numeric content, model identity, and any required normalization. Cosine calculations need nonzero vectors. Do not use a zero vector as a placeholder for missing content. Use an explicit status instead of inventing a meaningful coordinate.

Measure Exact VECTOR_DISTANCE Search Before Indexing

VECTOR_DISTANCE performs an exact calculation and does not use an approximate vector index. SQL Server must evaluate distances across the qualifying candidates to rank them. The cost grows with candidate count, dimensions, and concurrent searches. Measure your own eligible dataset rather than borrowing a universal cutoff.

DECLARE @SearchVector vector(3) = '[1,0,0]';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT TOP (3) ItemID,
       VECTOR_DISTANCE('cosine', @SearchVector, Coordinates) AS DistanceValue
FROM #VectorRows
ORDER BY DistanceValue, ItemID;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;

I keep an exact query as the reference when testing approximate retrieval. It establishes which neighbors the distance rule actually selects. Capture reference identifiers across representative queries, categories, and filters. The tiny table here demonstrates instrumentation, but it cannot establish performance at application scale.

Consider Approximation When the Workload Justifies It

An approximate vector index becomes useful when exhaustive ranking no longer meets the search budget. Approximation trades guaranteed nearest-neighbor retrieval for a less expensive candidate search. Evaluate recall against the exact reference, as well as response time and processor use. A faster result missing essential matches needs an explicit quality decision.

SQL Server 2025 approximate vector indexing and VECTOR_SEARCH are preview features. Check the documented requirements and supported build before testing them. On my SQL Server 2025 build, CREATE VECTOR INDEX was rejected as an unknown object type until the PREVIEW_FEATURES database scoped configuration was turned on. The approximate path requires its search function and index. Adding that index does not silently convert this post's scalar distance queries into approximate searches.

Also test filters and changing data. Measure how many eligible candidates survive the approximate search strategy. Include index storage, maintenance, and refresh behavior in the decision. A retrieval benchmark that ignores authorization and ongoing writes leaves out two important parts of an operational database.

Decide What Similar Means to the Reader

Which result should count as relevant when the closest row is still a poor match? TOP always asks for a number of candidates. It does not ensure they meet a useful quality bar. Validate a threshold or rejection rule with representative labeled examples for your application.

Keep distance values, chosen metrics, and embedding versions in diagnostic evidence. Review search quality when the model or source corpus changes. VECTOR_DISTANCE gives you a reproducible mathematical comparison. The application still owns the meaning, eligibility, and usefulness of the rows that comparison returns.

Related reading on this blog: Trigram Matching: Finding Similar Words With an N-Gram Table and Optimized Locking in SQL Server 2025: What Changes for Blocking.

A vector distance is not a relevance guarantee, it is a numerical comparison within a defined coordinate space.

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