Implement vector data types and vector search

7 minutes

SQL Server 2025 includes native support for vector data types and vector search. This feature enables you to store high-dimensional vector embeddings alongside your relational data and perform similarity searches, making it possible to build applications that understand semantic relationships within your data.

Vector search addresses limitations in traditional database queries. Traditional databases excel at exact matches and structured queries, but they struggle with understanding context and meaning. Vector search changes this by enabling semantic understanding—the ability to find conceptually similar items even when they don't share exact keywords. For example, a search for "comfortable running shoes" can find products described as "cushioned athletic footwear" because their vector embeddings capture similar semantic meaning.

For developers, this means you can build intelligent applications without managing separate vector databases or complex data synchronization processes. By keeping your vectors alongside your relational data in SQL Server, you maintain ACID compliance, use existing security policies, use familiar T-SQL syntax, and simplify your architecture. This integration applies to scenarios like:

Semantic search and recommendations: Find relevant products, documents, or content based on meaning rather than just keywords
Question-answering systems: Power chatbots and virtual assistants that understand natural language queries over your enterprise data
Anomaly detection: Identify unusual patterns by finding data points that are semantically distant from normal behavior
Content deduplication: Detect similar or duplicate items even when they're worded differently
Personalization engines: Match user preferences to products or content based on nuanced similarity

Explore the vector data type

The vector data type in SQL Server 2025 is designed to store arrays of floating-point numbers efficiently. Vectors are commonly used to represent embeddings generated by AI models, where each dimension captures specific features or semantic characteristics of the data.

Explore key characteristics

Optimized storage format: Vectors are stored in an optimized binary format internally but exposed as JSON arrays for ease of use and compatibility.
Flexible precision: Each element in the vector can be stored using single-precision (4-byte) or half-precision (2-byte) floating-point values, allowing you to balance accuracy and storage efficiency.
Dimension support: SQL Server 2025 supports vectors with up to 1998 dimensions for single-precision and 3,996 dimensions for half-precision, accommodating a wide range of embedding models.

Create and store vectors

You can create vectors by casting JSON arrays to the vector data type. Here's an example:

-- Create a vector from a JSON array
DECLARE @v1 VECTOR(3) = '[1.0, -0.2, 30]';
DECLARE @v2 VECTOR(3) = JSON_ARRAY(1.0, -0.2, 30);

SELECT @v1 AS v1, @v2 AS v2;

This code demonstrates two ways to create a 3-dimensional vector: by directly casting a JSON string literal, or by using the JSON_ARRAY function. Both methods produce the same result, storing the values [1.0, -0.2, 30] in a vector data type.

To store vectors in a table:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name NVARCHAR(100),
    description NVARCHAR(MAX),
    embedding VECTOR(1536)  -- Common dimension for OpenAI embeddings
);

This code creates a table to store product information along with a 1536-dimensional vector embedding. The dimension size of 1536 is commonly used with OpenAI's text-embedding models, making this table ready to store embeddings generated from product descriptions.

Convert vectors to JSON

You can easily convert vectors back to JSON arrays for display or processing:

DECLARE @v VECTOR(3) = '[1.0, -0.2, 30]';
SELECT 
    CAST(@v AS NVARCHAR(MAX)) AS string_representation,
    CAST(@v AS JSON) AS json_representation;

This code converts a vector back to readable formats. The CAST to NVARCHAR(MAX) returns the vector as a string, while CAST to JSON returns it as a JSON array, which is useful for interoperability with applications and APIs.

Perform exact nearest neighbor search (k-NN)

Exact nearest neighbor (k-NN) search involves calculating the distance between a query vector and all vectors in your dataset, then returning the k closest matches. This method guarantees precise results but can be computationally intensive for large datasets.

Use the `VECTOR_DISTANCE` function

The VECTOR_DISTANCE function measures the similarity between two vectors using a specified distance metric:

DECLARE @query_vector VECTOR(1536) = AI_GENERATE_EMBEDDINGS(
    N'Pink Floyd music style' 
    USE MODEL Ada2Embeddings
);

SELECT TOP (10) 
    product_id, 
    product_name,
    VECTOR_DISTANCE('cosine', @query_vector, embedding) AS distance
FROM products
ORDER BY distance;

This query generates an embedding for the text "Pink Floyd music style" using the Ada2Embeddings model, then finds the 10 products with embeddings most similar to the query. The VECTOR_DISTANCE function calculates the cosine distance between the query vector and each product's embedding, with smaller distances indicating greater similarity.

Choose distance metrics

SQL Server 2025 supports several distance metrics:

Cosine similarity: Measures the angle between vectors, ideal for text embeddings
Euclidean distance: Measures straight-line distance in vector space
Dot product: Useful for normalized vectors

Determine when to use exact search

Exact search is recommended when:

You have fewer than 50,000 vectors to search
Your query predicates filter the dataset to a manageable size
You require perfect recall (100% accuracy)
The extra computational cost is acceptable

Implement approximate nearest neighbor search (ANN)

For larger datasets, approximate nearest neighbor (ANN) search provides a balance between speed and accuracy. SQL Server 2025 implements ANN using the DiskANN algorithm, which creates a graph-based index for efficient vector navigation.

Understand recall

Recall measures the proportion of true nearest neighbors that an ANN algorithm identifies compared to exact search. A recall of 1.0 (100%) means the approximate search returns the same results as exact search. In practice, recall values above 0.95 often provide excellent results for AI applications while offering significant performance improvements.

Create vector indexes

To enable ANN search, create a vector index on your vector column:

CREATE VECTOR INDEX idx_product_embedding 
ON products(embedding);

This code creates a vector index on the embedding column using the DiskANN algorithm. The index improves the performance of approximate nearest neighbor searches on large datasets by creating a graph structure for efficient vector navigation.

Vector indexes in SQL Server 2025:

Use the DiskANN algorithm for efficient graph-based search
Support limited memory and CPU resources
Balance disk I/O, memory usage, and query performance
Automatically update as data changes

Use the `VECTOR_SEARCH` function

The VECTOR_SEARCH function performs approximate nearest neighbor searches:

DECLARE @query_vector VECTOR(1536) = AI_GENERATE_EMBEDDINGS(
    N'Pink Floyd music style' 
    USE MODEL Ada2Embeddings
);

SELECT 
    t.product_id,
    t.product_name,
    s.distance
FROM
    VECTOR_SEARCH(
        TABLE = products AS t, 
        COLUMN = embedding, 
        SIMILAR_TO = @query_vector, 
        METRIC = 'cosine', 
        TOP_N = 10
    ) AS s
ORDER BY s.distance;

This query performs an approximate nearest neighbor search using the vector index created earlier. The VECTOR_SEARCH function uses the DiskANN algorithm to find the 10 most similar products without scanning all vectors, providing performance improvements for large datasets while maintaining high accuracy.

Consider performance benefits

ANN search offers:

Faster query execution: Especially for datasets with millions of vectors
Lower resource consumption: Reduced CPU and memory usage compared to exact search
Scalability: Handles large-scale vector datasets efficiently
High recall: Typically achieves recall rates above 95%, ensuring quality results

Build hybrid search scenarios

SQL Server 2025 supports combining vector search with traditional SQL operations for hybrid search scenarios.

Combine semantic and keyword search

You can perform semantic search using vectors while also applying traditional filters:

DECLARE @query_vector VECTOR(1536) = AI_GENERATE_EMBEDDINGS(
    N'comfortable running shoes' 
    USE MODEL Ada2Embeddings
);

SELECT 
    t.product_id,
    t.product_name,
    t.category,
    t.price,
    s.distance
FROM
    VECTOR_SEARCH(
        TABLE = products AS t, 
        COLUMN = embedding, 
        SIMILAR_TO = @query_vector, 
        METRIC = 'cosine', 
        TOP_N = 20
    ) AS s
WHERE 
    t.category = 'Footwear'
    AND t.price BETWEEN 50 AND 150
ORDER BY s.distance;

This hybrid search combines semantic similarity search with traditional SQL filtering. First, it finds the 20 most semantically similar products to "comfortable running shoes," then filters those results to only include footwear items priced between 50 USD and 150 USD. This approach enables precise targeting while using semantic understanding.

Integrate full-text and vector search

Combine full-text search with vector search for comprehensive results:

DECLARE @query_vector VECTOR(1536) = AI_GENERATE_EMBEDDINGS(
    N'sustainable materials' 
    USE MODEL Ada2Embeddings
);

SELECT 
    t.product_id,
    t.product_name,
    s.distance,
    fts.RANK AS text_rank
FROM
    VECTOR_SEARCH(
        TABLE = products AS t, 
        COLUMN = embedding, 
        SIMILAR_TO = @query_vector, 
        METRIC = 'cosine', 
        TOP_N = 50
    ) AS s
INNER JOIN CONTAINSTABLE(products, description, 'sustainable OR eco-friendly') AS fts
    ON t.product_id = fts.[KEY]
ORDER BY (s.distance * 0.6) + ((1.0 - fts.RANK/1000.0) * 0.4);

This query combines vector search with full-text search. It finds products semantically similar to "sustainable materials" using vector embeddings, then joins with full-text search results for keywords "sustainable" or "eco-friendly" in the description. The final ranking combines both scores with a weighted formula (60% semantic similarity, 40% keyword match).

Apply best practices

When implementing vector search in SQL Server 2025:

Choose the right precision: Use half-precision for larger dimensions when storage is a concern
Index strategically: Create vector indexes on columns that are frequently searched
Monitor recall: Test your ANN queries to ensure acceptable recall rates for your use case
Optimize queries: Use appropriate filters to reduce the search space before vector operations
Batch operations: Generate and insert embeddings in batches for better performance

By using these vector capabilities, you can build AI-powered applications that understand semantic relationships, provide intelligent recommendations, and deliver natural language search experiences—all within the trusted SQL Server platform.

Feedback

Was this page helpful?