SQL Server 2025 Vector Search: VECTOR Data Type, Exact Search, and DiskANN

DBA PARK may earn a commission from purchases through links in this article, at no extra cost to you.

SQL Server 2025 can store embeddings in a native VECTOR column and compare them with T-SQL. This matters when the text, permissions, tenant ID, status, and embedding belong to the same business row: you can keep relational filters and semantic search in one database instead of synchronizing a separate vector store.

The feature has two distinct search paths. VECTOR_DISTANCE calculates an exact distance but normally scans every candidate row. A vector index uses DiskANN to return approximate nearest neighbors with much lower search cost at scale. Treat those as different engineering choices, not interchangeable syntax.

What You’ll Learn: how to create a VECTOR column, run exact cosine-distance queries, enable the preview vector index, query DiskANN, and measure recall before production.

1. Prerequisites and Feature Status

• SQL Server 2025 (17.x) for the native VECTOR type.
• A client tool that understands SQL Server 2025 metadata.
• Embeddings generated by your application or an embedding model. SQL Server stores and searches vectors; it does not make an arbitrary text value semantically meaningful by itself.
• For CREATE VECTOR INDEX, enable the database-scoped PREVIEW_FEATURES option and review the current preview limitations.

Approximate vector indexes remain a preview feature in SQL Server 2025. Validate the current cumulative update, limitations, DML behavior, and backup/restore path before relying on them in production.

2. Create a Small Reproducible Lab

Use five dimensions only to keep the example readable. A production embedding must use the exact dimension count produced by its model.

CREATE DATABASE VectorLab;
GO
USE VectorLab;
GO

CREATE TABLE dbo.KnowledgeArticle
(
    article_id  int IDENTITY(1,1) PRIMARY KEY,
    tenant_id   int          NOT NULL,
    title       nvarchar(200) NOT NULL,
    body        nvarchar(max) NOT NULL,
    embedding   vector(5)     NOT NULL
);

INSERT dbo.KnowledgeArticle (tenant_id, title, body, embedding)
VALUES
(1, N'Backup strategy', N'Full, differential, and log backups', '[0.91,0.12,0.07,0.02,0.11]'),
(1, N'Restore testing', N'Validate backups by restoring them',  '[0.88,0.16,0.09,0.03,0.08]'),
(1, N'Index tuning',    N'Reduce reads with useful indexes',    '[0.10,0.84,0.21,0.07,0.05]'),
(2, N'Private article', N'Another tenant row',                  '[0.90,0.11,0.06,0.02,0.12]');

3. Run an Exact Similarity Search

Cosine distance is smaller when vectors are more similar. Apply selective relational predicates before ordering the remaining rows by distance.

DECLARE @query vector(5) = '[0.90,0.14,0.08,0.02,0.10]';

SELECT TOP (3)
       article_id,
       title,
       VECTOR_DISTANCE('cosine', @query, embedding) AS distance
FROM dbo.KnowledgeArticle
WHERE tenant_id = 1
ORDER BY distance, article_id;

This query is the ground-truth baseline for a recall test. It is also reasonable for small or highly filtered candidate sets. On a large unfiltered table, however, the engine must calculate a distance for many rows and sort the results.

4. Create a DiskANN Vector Index

ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;
GO

CREATE VECTOR INDEX IX_KnowledgeArticle_Embedding
ON dbo.KnowledgeArticle (embedding)
WITH
(
    METRIC = 'cosine',
    TYPE = 'DiskANN',
    MAXDOP = 4
);

The metric used by the query must match the metric used by the vector index. DiskANN is currently the supported ANN index type. MAXDOP limits resource consumption during the build; it is not a query-time recall control.

SELECT i.name, vi.vector_index_type, vi.distance_metric
FROM sys.vector_indexes AS vi
JOIN sys.indexes AS i
  ON i.object_id = vi.object_id
 AND i.index_id  = vi.index_id
WHERE vi.object_id = OBJECT_ID(N'dbo.KnowledgeArticle');

5. Query the Approximate Index

SQL Server 2025 uses the preview VECTOR_SEARCH table-valued function. The legacy index version accepts TOP_N; newer Azure SQL vector indexes use SELECT TOP (N) WITH APPROXIMATE. Check the documentation for the engine and index version you actually run.

DECLARE @query vector(5) = '[0.90,0.14,0.08,0.02,0.10]';

SELECT s.distance, a.article_id, a.title
FROM VECTOR_SEARCH(
    TABLE      = dbo.KnowledgeArticle AS a,
    COLUMN     = embedding,
    SIMILAR_TO = @query,
    METRIC     = 'cosine',
    TOP_N      = 10
) AS s
WHERE a.tenant_id = 1
ORDER BY s.distance, a.article_id;

6. Production Validation Checklist

7. When SQL Server Is the Right Vector Store

• Choose it when semantic search must be combined with transactional rows, row-level security, temporal data, or familiar T-SQL operations.
• Use exact search for small or tightly filtered sets and as the quality baseline.
• Use approximate search only after measuring acceptable recall and latency.
• Consider a specialized search service when search relevance features, independent scaling, or cross-source ingestion dominate the workload.

Summary

The native VECTOR type removes an awkward storage gap, while DiskANN adds a scalable approximate path. The safe design is exact search first, measured ANN second, and explicit monitoring for model version, filters, recall, and tail latency.

Continue learning: To explore the retrieval concepts behind this SQL Server example, browse Udemy and search for RAG and vector database courses with exercises on embeddings, similarity search, and retrieval evaluation. For guided practice with relational database design and SQL, explore DataCamp.

Related DBA park Guides

• Best Database for RAG in 2026
• DuckDB for SQL Server DBAs

Official References

• Microsoft Learn: Vector search and vector indexes in the SQL Database Engine
• Microsoft Learn: VECTOR data type
• Microsoft Learn: CREATE VECTOR INDEX