hybrid search
1 TopicFrom Exact Search to DiskANN: Building Practical Vector Search in Azure SQL
Storing embeddings in Azure SQL is the easy part. Add a VECTOR column, load some data, and you're done. The harder part is getting the right results quickly for the right user, while the data keeps changing underneath you. This post covers the decisions that actually matter: exact or approximate search, which distance metric to use, how filters behave, and how to check that your index is helping. Where things stand DiskANN vector indexes and VECTOR_SEARCH are now generally available in Azure SQL Database, SQL database in Microsoft Fabric, and Azure SQL Managed Instance on the Always-up-to-date update policy. In SQL Server 2025, and on Managed Instance with the SQL Server 2025 policy, they're still in preview. The real advantage isn't speed. Your vectors live in the same database as your operational data, so you can filter, join, secure and apply business logic to them with ordinary T-SQL. You don't need a separate vector store that has to be kept in sync. A simple model to work with All the examples use a synthetic, multi-tenant document table: CREATE TABLE dbo.Documents ( DocumentId INT IDENTITY PRIMARY KEY, TenantId INT NOT NULL, Category NVARCHAR(50) NOT NULL, Title NVARCHAR(200) NOT NULL, Embedding VECTOR(1536) NULL ); Exact first, approximate when you need it You don't need an index to start. If you run VECTOR_SEARCH with TOP (N) and ORDER BY distance, but without WITH APPROXIMATE, you get an exact k-nearest-neighbor search. It compares the query vector with every row: SELECT TOP (10) d.DocumentId, d.Title, r.distance FROM VECTOR_SEARCH( TABLE = dbo.Documents AS d, COLUMN = Embedding, SIMILAR_TO = @qv, METRIC = 'cosine') AS r ORDER BY r.distance; -- exact kNN For small tables, dev environments, or cases where you need perfect accuracy, that's often all you need. Exact search becomes a problem as the table grows, because every query scans everything. That's where DiskANN comes in. It's an approximate nearest-neighbor (ANN) index: you give up a little recall in exchange for much less work per query. How much recall you're willing to lose is a business decision, not a technical one. Choosing a distance metric Three metrics are supported: cosine, euclidean and dot (negative dot product). My practical advice is to use whatever your embedding model recommends. For most text-embedding models that's cosine. Whatever you choose, use the same metric in the index and in the query. If they don't match, the engine can't use the ANN index. It raises a warning and falls back to kNN, and that fallback is easy to miss. Creating the index CREATE VECTOR INDEX IX_Documents_Embedding ON dbo.Documents (Embedding) WITH (METRIC = 'cosine', TYPE = 'DiskANN'); Two things catch people out: You need at least 100 rows with non-NULL vectors. With fewer, creation fails with error 42266. Load your data first, then build the index. The query syntax changed. With the current index version, approximate search uses SELECT TOP (N) WITH APPROXIMATE. The old TOP_N parameter is deprecated, and with a new index it fails with error 42274. SELECT TOP (10) WITH APPROXIMATE d.DocumentId, d.Title, r.distance FROM VECTOR_SEARCH( TABLE = dbo.Documents AS d, COLUMN = Embedding, SIMILAR_TO = @qv, METRIC = 'cosine') AS r ORDER BY r.distance; ORDER BY must reference only the distance column, in ascending order. If you need GROUP BY, window functions, DISTINCT or a secondary sort, run the vector search in a subquery and do the rest in the outer query. Filters are where it gets interesting Real queries rarely ask only "what's similar?" They ask "what's similar for this tenant, in this category?" SELECT TOP (10) WITH APPROXIMATE d.DocumentId, d.Title, r.distance FROM VECTOR_SEARCH( TABLE = dbo.Documents AS d, COLUMN = Embedding, SIMILAR_TO = @qv, METRIC = 'cosine') AS r WHERE d.TenantId = @TenantId AND d.Category = N'Policy' ORDER BY r.distance; With current-version indexes, WHERE predicates are applied during the search, not after it. The engine keeps searching until it finds 10 qualifying rows or runs out of candidates. Earlier index versions filtered afterwards, so a selective tenant filter could return three rows, or none, even when matches existed. Add an ordinary B-tree index on the columns you filter on, such as TenantId and Category. The optimizer can use both indexes together, and that helps most when the filters are selective. The optimizer also decides for itself whether to use the DiskANN index or do an exact kNN scan. A very selective filter can make a scan the better choice. If you're benchmarking and want to force the ANN path, use the FORCE_ANN_ONLY table hint. I'd leave it out of production code unless you've measured a good reason for it. Data keeps changing, and that's fine now Current-version indexes support INSERT, UPDATE, DELETE and MERGE. You don't need to rebuild the index, and changes show up in search results as soon as the transaction commits. The changes are folded into the index itself in the background, though. Until that catches up, new rows are still searchable, but after a big load, queries can run a bit slower or return less accurate matches for a while. You can track this with the graph_catchup_pending_percent column in sys.dm_db_vector_indexes. A nonzero value just after a bulk load is normal. A value that stays high, or a failed background task, is worth looking into. The exception is a near-total refresh, such as re-embedding everything with a new model. The old index structure won't match the new data, and recall can drop. In that case, drop and recreate the index after the load. Don't rebuild just because the catch-up number is briefly above zero. Check what you actually have If an index was created a while ago, check its version: SELECT i.name, t.name AS table_name, JSON_VALUE(v.build_parameters, '$.Version') AS index_version FROM sys.vector_indexes v JOIN sys.indexes i ON v.object_id = i.object_id AND v.index_id = i.index_id JOIN sys.tables t ON v.object_id = t.object_id; Version 3 or higher is current. Anything older uses post-filtering and needs a drop and recreate. Plan that for a maintenance window, because approximate search is unavailable until the new index is built. Measure, don't assume DiskANN isn't automatically faster, and it isn't automatically the right design. Run a small benchmark against your own data: Pick 50–100 representative query vectors, with realistic filters. For each one, run the exact query (no APPROXIMATE) and save the top 10 IDs as your baseline. Run the approximate query and record its latency and IDs. Recall@10 is the share of baseline IDs the approximate query also returned. Report it next to p50 and p95 latency. Then decide. If recall is 0.95 at a fraction of the latency, that's a good deal for most retrieval-augmented generation (RAG) workloads. If your filters are so selective that the optimizer keeps choosing kNN anyway, the index may not be paying for itself. A few limits worth knowing You can't use TRUNCATE TABLE on a table with a vector index. Drop the index, truncate, reload at least 100 rows, then recreate the index. Vector indexes can't be partitioned and aren't replicated to subscribers. VECTOR_SEARCH can't be used inside a view. Deduplicate embeddings before you index. Lots of identical vectors crowd out better matches. When to look elsewhere If you need heavy full-text relevance tuning, faceted navigation, or search over content that isn't in your database, a dedicated search service may still be the better fit. But if your vectors describe rows you already store, secure and query in SQL, keeping the search there usually means fewer moving parts and one less sync job. Reference: CREATE VECTOR INDEX (Transact-SQL) - SQL Server | Microsoft Learn VECTOR_SEARCH (Transact-SQL) - SQL Server | Microsoft Learn