Vector indexes and vector search in SQL database in Fabric

SQL database in Microsoft Fabric now features general availability for the VECTOR data type, distance functions, and vector indexes. This allows you to store embeddings directly alongside relational data and perform similarity searches without external services.

Use cases

Imagine you have a database of articles and need to search contents by semantic similarity to answer user questions.

• Store numeric arrays (embeddings) efficiently.
• Calculate the distance between vectors using the VECTOR_DISTANCE function.
• Run exact (kNN) and approximate (ANN) searches using DiskANN-based indexes.

How to use

You can create vectors by casting JSON arrays or using embedding functions and run exact search queries.

DECLARE @qv VECTOR(1536) = AI_GENERATE_EMBEDDINGS(N'Pink Floyd music style' USE MODEL Ada2Embeddings);
SELECT TOP (10) id, VECTOR_DISTANCE('cosine', @qv, [content_vector]) AS distance, title
FROM [dbo].[wikipedia_articles_embeddings]
ORDER BY distance;

What happens in this code:

1. Declares a variable @qv of type VECTOR(1536) populated with an embedding generated from text.
2. Queries the wikipedia_articles_embeddings table to find the closest records.
3. Uses the VECTOR_DISTANCE function with the 'cosine' metric to calculate the distance between the query vector and the stored column.
4. Orders by the shortest distance and returns the top 10 results.

Limitations and requirements

• The feature is generally available (GA) in SQL database in Microsoft Fabric.
• Exact search is recommended for datasets under 50,000 vectors, while vector indexes help scale approximate searches.