Returning five similar rows does not mean SQL Server compares only five embeddings. Exact vector search evaluates distances across the eligible candidate set before selecting the nearest rows. A useful relational filter can shrink that set and reduce the work without changing the distance calculation.
Define the Candidate Set for Exact Vector Search
SQL Server 2025 provides the vector data type and VECTOR_DISTANCE. An exact query computes the requested metric for eligible stored vectors and orders by that value. For cosine distance, lower values identify closer candidates under that metric's definition.
I define eligibility before discussing search cost. Category, tenant, status, and date rules can reduce the candidate set when they belong to the business request. A filter invented solely to make the benchmark faster changes which nearest neighbors the query is allowed to return.
Use embeddings with the dimension and generation contract the application requires. The demonstration uses small synthetic nonzero vectors so the calculation remains readable. It does not claim semantic quality or a real model's output. TOP limits the response. It does not give the engine permission to skip comparing unrelated candidates unless the query's eligible set excludes them.
Keep the candidate filter visible in an exact vector search, because it defines which rows can qualify as neighbors.
Build a Controlled SQL Server 2025 Table
The setup creates a dedicated table with a vector column and a relational category. Its generated values are synthetic test inputs. Run it only on SQL Server 2025 in a disposable database with sufficient resources for the chosen population.
The clustered primary key provides stable identity and a tie breaker. Many sample vectors intentionally repeat, so ID makes the TOP result deterministic among equal distances. A production query needs an appropriate tie rule too. An apparently stable order in one execution is not a contract without ORDER BY.
I keep the vector dimension explicit in both the table and the query variable. A mismatch is a data-contract problem, not a tuning issue. Preserve the relational metadata that can legitimately filter the search. Those columns can be indexed independently of the vector and are useful even when the exact distance calculation remains a scalar ranking operation.
CREATE TABLE dbo.ExactVectorDemo
(ID int NOT NULL PRIMARY KEY,CategoryID int NOT NULL,TitleText varchar(40),Embedding vector(3));
WITH Numbers AS
(
SELECT TOP(100000) ROW_NUMBER() OVER(ORDER BY a.object_id,b.object_id) AS n
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT dbo.ExactVectorDemo
SELECT CONVERT(int,n),CASE WHEN n%100=0 THEN 10 ELSE 20 END,'Sample',
CAST(CASE WHEN n%2=0 THEN '[1,0,0]' ELSE '[0,1,0]' END AS vector(3))
FROM Numbers;Capture the Unfiltered Exact Vector Search Plan
Enable actual plans in SSMS and capture IO and time. The next query calculates cosine distance and returns the leading results under a deterministic order. Inspect the table access, distance computation, and Top N Sort or equivalent ranking work.
Without a specialized search structure, an unfiltered exact query must evaluate the eligible vectors before it knows which candidates belong in the result. The optimizer's exact operator layout can vary, so report the actual plan rather than assuming every build renders the same icons.
What rows reached the distance calculation? Check the input cardinality and the logical request. A returned set containing only a few rows says little about that input size. Keep server execution evidence separate from client display time and the work used to generate the query vector. The demonstration isolates the stored-vector comparison rather than claiming a complete end-to-end application benchmark.
DECLARE @QueryVector vector(3)=CAST('[1,0,0]' AS vector(3));
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT TOP(5) ID,CategoryID,TitleText,
VECTOR_DISTANCE('cosine',Embedding,@QueryVector) AS DistanceValue
FROM dbo.ExactVectorDemo
ORDER BY DistanceValue,ID;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
Add an Indexed Filter That Belongs to the Request
The next index supports the category predicate. It includes the display column and the vector itself, so a seek can return every needed column without key lookups. The vector sits only in INCLUDE, never in the B-tree key. The filtered request restricts eligibility before ordering distances. Compare its actual access path and candidate count with the unfiltered query.
The filter changes the search domain intentionally. Its result means nearest within category ten, not nearest across the whole table. Confirm that this is the request the application needs. A tenant boundary or an approved date window can serve the same role when it belongs to the contract.
The optimizer can still choose a scan for a broad category or another distribution. Without the vector in INCLUDE, a seek needs one lookup per candidate, and the optimizer can pick a full scan instead. Inspect the complete plan and your own IO evidence. The index creates a selective access opportunity; it does not guarantee a fixed improvement for every category value.
CREATE INDEX IX_ExactVector_Category ON dbo.ExactVectorDemo(CategoryID) INCLUDE(TitleText,Embedding);
DECLARE @QueryVector vector(3)=CAST('[1,0,0]' AS vector(3));
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT TOP(5) ID,CategoryID,TitleText,
VECTOR_DISTANCE('cosine',Embedding,@QueryVector) AS DistanceValue
FROM dbo.ExactVectorDemo
WHERE CategoryID=10
ORDER BY DistanceValue,ID;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;Measure Growth in Candidates and Dimensions
Exact distance work grows with the number of vectors examined and the work required per vector. Dimensions, candidate population, and result-ranking work all contribute. A relational filter reduces candidates; it does not make exact comparison independent of input size.
Test selective and broad filters with representative dimensions and stored values. Keep the metric, query vector, requested result count, and server conditions consistent within each comparison. The sample's tiny dimension count is for readability and does not model a larger embedding's cost automatically.
Inspect memory and sort behavior as well as table IO. Returning a small TOP set can reduce retained ranking state, but it does not remove the distance evaluation across candidates. Also check concurrency when several searches run together. A single acceptable execution does not establish the capacity of a continuously called search path under normal application load.
Keep Preview Approximate Search Outside This Test
Approximate vector indexes and associated approximate search remain preview features in SQL Server 2025 and are outside this exact-search example. They introduce a different search strategy and result-quality contract. Do not quietly substitute them and label the result an exact-query optimization.
Validate known vectors, equal distances, NULL policy, and the required eligibility boundaries. Keep vector generation and dimensional compatibility tested separately. A fast distance query cannot repair an embedding representation that does not match its intended source.
Exact vector search is practical when the eligible set and dimensions fit the workload. Start with the actual plan, use legitimate indexed filters to reduce candidates, and compare representative measurements. The useful result preserves the meaning of nearest within the approved search domain while making the work visible and manageable.
Related reading on this blog: Vector Search in SQL Server 2025 With VECTOR_DISTANCE and Local AI Models for SQL Server: A Complete Guide.
A small nearest-neighbor result is not a small search, it is a ranking over the candidates you allowed.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
