Edit

Share via

VECTOR_SEARCH (Transact-SQL) (Preview)

Applies to: SQL Server 2025 (17.x) Preview

Search for vectors similar to a given query vectors using an approximate nearest neighbors vector search algorithm. To learn more about how vector indexing and vector search works, and the differences between exact and approximate search, refer to Vector search and vector indexes in the SQL Database Engine.

Preview feature

This function is in preview and is subject to change. In order to use this feature, you must enable the PREVIEW_FEATURES database scoped configuration.

Make sure to check out the current limitations before using it.

Syntax

Transact-SQL syntax conventions

VECTOR_SEARCH(
    TABLE = object [ AS source_table_alias ]
    , COLUMN = vector_column
    , SIMILAR_TO = query_vector
    , METRIC = { 'cosine' | 'dot' | 'euclidean' }
    , TOP_N = k
) [ AS result_table_alias ]

Arguments

TABLE = object [AS source_table_alias]

Table on which perform the search. It must be a base table. Views, temporary tables, both local and global, aren't supported.

COLUMN = vector_column

The vector column in which search is performed. The column must be a vector data type.

SIMILAR_TO = query_vector

The vector used for search. It must be a variable or a column of vector type.

METRIC = { 'cosine' | 'dot' | 'euclidean' }

The distance metric used to calculate the distance between the query vector and the vectors in the specified column. An ANN (Approximate Nearest Neighbor) index is used only if a matching ANN index, with the same metric and on the same column, is found. If there are no compatible ANN indexes, a warning is raised and the KNN (k-Nearest Neighbor) algorithm is used.

TOP_N = <k>

The maximum number of similar vectors that must be returned. It must be a positive integer.

result_table_alias

The alias is used to reference the result set.

Return result set

The result set returned by the VECTOR_SEARCH function includes:

  • All columns from the table specified in the TABLE argument.
  • An additional column named distance, which represents the distance between the vector in the column specified by the COLUMN argument and the vector provided in the SIMILAR_TO argument.

The distance column is generated by the VECTOR_SEARCH function itself, while all other columns come from the table referenced in the TABLE argument.

If you use an alias for the table in the TABLE argument, you must use that same alias to reference its columns in the SELECT statement. You can't use the alias assigned to VECTOR_SEARCH to reference columns from the table specified in TABLE. This behavior is easier to understand if you think of the result set built by taking the output of VECTOR_SEARCH and merging it with the table data.

If the table specified in the TABLE argument already contains a column named distance, the behavior will be similar to a SQL join between two tables that share a column name. In such cases, you must use table aliases to disambiguate the column references—otherwise, an error will be raised.

Limitations

The current preview has the following limitations:

Post-filter only

Vector search happens before applying any predicate. Additional predicates are applied only after the most similar vectors are returned. The following sample returns the top 10 rows with embeddings most similar to the query vector @qv, then applies the predicate specified in the WHERE clause. If none of the 10 rows associated with the vectors returned by the vector search have the accepted column equal to 1, the result is empty.

SELECT TOP (10) s.id,
                s.title,
                r.distance
FROM VECTOR_SEARCH(
         TABLE = dbo.sessions AS s,
         COLUMN = embedding,
         SIMILAR_TO = @qv,
         METRIC = 'cosine',
         TOP_N = 10
     ) AS r
WHERE accepted = 1
ORDER BY r.distance;

VECTOR_SEARCH can't be used in views

VECTOR_SEARCH can't be used in the body of a view.

Examples

Example 1

The following example finds the 10 most similar articles to the Pink Floyd music style in the wikipedia_articles_embeddings table.

DECLARE @qv VECTOR(1536) = AI_GENERATE_EMBEDDING(N'Pink Floyd music style' USE MODEL Ada2Embeddings);
SELECT TOP (10) s.id,
                s.title,
                r.distance
FROM VECTOR_SEARCH(
        TABLE = [dbo].[wikipedia_articles_embeddings] as t,
        COLUMN = [content_vector],
        SIMILAR_TO = @qv,
        METRIC = 'cosine',
        TOP_N = 10
    ) AS s
ORDER BY s.distance

Example 2

Same as example 1, but this time the query vectors are taking from another table instead of a variable.

CREATE TABLE #t
(
    id INT,
    q NVARCHAR (MAX),
    v VECTOR(1536)
);

INSERT INTO #t
SELECT id,
       q,
       AI_GENERATE_EMBEDDINGS(q USE MODEL Ada2Embeddings)
FROM (VALUES
        (1, N'four legged furry animal'),
        (2, N'pink floyd music style')
    ) S(id, q);

SELECT TOP (10) t.id,
                s.distance,
                t.title
FROM #t AS qv
CROSS APPLY
    VECTOR_SEARCH(
        TABLE = [dbo].[wikipedia_articles_embeddings] AS t,
        COLUMN = [content_vector],
        SIMILAR_TO = qv.v,
        METRIC = 'cosine',
        TOP_N = 10
    ) AS s
WHERE qv.id = 2
ORDER BY s.distance;

Example 3

A basic end-to-end example using CREATE VECTOR INDEX and the related VECTOR_SEARCH function. The embeddings are mocked. In a real world scenario, embeddings are generated using an embedding model and AI_GENERATE_EMBEDDINGS, or an external library such as OpenAI SDK.

The following code block demonstrates the VECTOR_SEARCH function with mock embeddings:

  1. Enables the trace flag, necessary in the current preview.
  2. Create a sample table dbo.Articles with a column embedding with data type vector(5).
  3. Insert sample data with mock embedding data.
  4. Create a vector index on dbo.Articles.embedding.
  5. Demonstrate the vector similarity search with the VECTOR_SEARCH function.
-- Step 0: Enable Preview Feature
ALTER DATABASE SCOPED CONFIGURATION
SET PREVIEW_FEATURES = ON;
GO

-- Step 1: Create a sample table with a VECTOR(5) column
CREATE TABLE dbo.Articles
(
    id INT PRIMARY KEY,
    title NVARCHAR(100),
    content NVARCHAR(MAX),
    embedding VECTOR(5) -- mocked embeddings
);
GO

-- Step 2: Insert sample data
INSERT INTO Articles (id, title, content, embedding)
VALUES
(1, 'Intro to AI', 'This article introduces AI concepts.', '[0.1, 0.2, 0.3, 0.4, 0.5]'),
(2, 'Deep Learning', 'Deep learning is a subset of ML.', '[0.2, 0.1, 0.4, 0.3, 0.6]'),
(3, 'Neural Networks', 'Neural networks are powerful models.', '[0.3, 0.3, 0.2, 0.5, 0.1]'),
(4, 'Machine Learning Basics', 'ML basics for beginners.', '[0.4, 0.5, 0.1, 0.2, 0.3]'),
(5, 'Advanced AI', 'Exploring advanced AI techniques.', '[0.5, 0.4, 0.6, 0.1, 0.2]');
GO

-- Step 3: Create a vector index on the embedding column
CREATE VECTOR INDEX vec_idx ON Articles(embedding)
WITH (METRIC = 'cosine', TYPE = 'diskann');
GO

-- Step 4: Perform a vector similarity search
DECLARE @qv VECTOR(5) = '[0.3, 0.3, 0.3, 0.3, 0.3]';
SELECT TOP(3)
    t.id,
    t.title,
    t.content,
    s.distance
FROM
    VECTOR_SEARCH(
        TABLE = Articles AS t,
        COLUMN = embedding,
        SIMILAR_TO = @qv,
        METRIC = 'cosine',
        TOP_N = 3
    ) AS s
ORDER BY s.distance, t.title;

Related content