SQL Server 2025 introduces native support for vector data types, searches, and indexing directly within the SQL Database Engine. This allows developers and data professionals to store, index, and query high-dimensional vector embeddings—numerical representations of data like text, images, or audio—alongside traditional structured and unstructured data. Vector indexing is particularly useful for AI-driven applications, such as semantic search, recommendation systems, and retrieval-augmented generation (RAG), without needing external vector databases or services.
Key Concepts and Terminology
- Vectors and Embeddings: A vector is an ordered array of floating-point numbers (e.g., [0.1, 0.2, 0.3]) that captures the semantic meaning of data. Embeddings are these vectors generated by machine learning models, like those from Azure OpenAI, to enable similarity comparisons.
- Distance Metrics: Measures of similarity between vectors:
- Cosine Distance: Focuses on direction (angle) between vectors, ideal for text embeddings.
- Euclidean Distance: Measures straight-line distance, sensitive to magnitude.
- Dot Product: Computes the (negative) scalar product, often used for normalized vectors.
- Nearest Neighbor Search (k-NN): Finds the top-k most similar vectors to a query vector.
- Exact k-NN: Brute-force calculation across all vectors—accurate but slow for large datasets (<50,000 vectors recommended).
- Approximate Nearest Neighbor (ANN): Uses indexes for faster queries with slight accuracy trade-offs, scaling to millions of vectors.
Vector indexing in SQL Server 2025 powers ANN searches using the DiskANN algorithm, a graph-based method optimized for SSD storage that balances speed, recall, and resource efficiency.
Key Features
- VECTOR Data Type: Stores vectors as binary-optimized arrays, with dimensions up to 16,000. Vectors can be created via casting from JSON arrays or built-in functions like AI_GENERATE_EMBEDDINGS.
- Built-in Functions:
- VECTOR_DISTANCE(metric, query_vector, target_vector): Computes distance between two vectors.
- VECTOR_SEARCH: Performs ANN queries on indexed columns.
- Performance Benefits: Indexes reduce query time from O(n) (exact search) to near-constant time for large datasets, leveraging SSDs for low-latency graph traversal.
- Integration: Works seamlessly with T-SQL, no external dependencies required.
How Vector Indexing Works
- Storage: Vectors are stored in a dedicated VECTOR column.
- Index Creation: A vector index builds a DiskANN graph structure on the column, partitioning vectors into clusters for efficient navigation. The index uses the specified metric (e.g., cosine) to organize relationships.
- Querying: During ANN search, the engine starts from entry points in the graph, traverses to similar neighbors, and refines results—achieving high recall (e.g., 95%+) while minimizing CPU/memory use.
- Trade-offs: Approximate searches are faster but may miss some exact matches; tune via index parameters for your workload.
Supported Platforms
- SQL Server 2025 (17.x) Preview (full support for indexes).
- Azure SQL Database and Managed Instance (with 2025 update policy).
- SQL in Microsoft Fabric (preview).
Vector features are in active development; check Microsoft Docs for updates. For hands-on demos, explore sample databases like those for Wikipedia embeddings.
