{"id":7435,"date":"2026-08-17T04:22:09","date_gmt":"2026-08-17T11:22:09","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=7435"},"modified":"2026-08-27T21:33:31","modified_gmt":"2026-08-28T04:33:31","slug":"beyond-vector-indexes-azure-sql-brings-optimizer-intelligence-to-vector-search","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/beyond-vector-indexes-azure-sql-brings-optimizer-intelligence-to-vector-search\/","title":{"rendered":"Beyond Vector Indexes: Azure SQL Brings Optimizer Intelligence to Vector Search"},"content":{"rendered":"

Why p<\/span>roduction AI retrieval depends on more<\/strong> than vector index <\/span>performance.<\/span><\/span>\u00a0<\/span><\/em><\/p>\n

It’s easy to think of AI retrieval as a vector search problem. For many developers, the first conversation starts with vector index benchmarks: How many queries per second can it handle? What’s the latency? What’s the recall? How well does it scale?<\/p>\n

Those metrics are critical, but production AI retrieval rarely consists of vector similarity alone. Equally important is what happens when vector search becomes part of a real application workload, one that includes filters, joins, security policies, ranking logic, and live operational data.<\/p>\n

Generating similar candidates at scale is only the first part of the problem. The harder challenge is helping the database find the right results after the rest of the SQL query is considered<\/p>\n

The reality of production AI retrieval<\/span><\/b>\u00a0<\/span><\/h3>\n

Consider an enterprise support application where a support engineer investigates VPN failures after a password reset. The engineer searches for “VPN connection fails after password reset” to find similar incidents and known resolutions.<\/span>\u00a0<\/span><\/p>\n

Finding semantically similar tickets is only part of the task. The engineer also needs results that are still open, belong to the relevant product area, include customer information from related tables, and are visible under existing security policies. Some of these conditions depend on values known only when the query executes, such as the selected product area, tenant, or current user context.<\/span>\u00a0<\/span><\/p>\n

Conceptually, the query might look like this:<\/span><\/span><\/p>\n

DECLARE @ProductArea NVARCHAR(50) = 'Networking'; \r\nDECLARE @TenantId UNIQUEIDENTIFIER = @CurrentTenantId; \r\n \r\nSELECT TOP (10) WITH APPROXIMATE \r\n    t.TicketId, \r\n    t.Title, \r\n    t.PriorityLevel, \r\n    t.TicketStatus, \r\n    c.CustomerName, \r\n    p.ProductName, \r\n    s.distance \r\nFROM VECTOR_SEARCH( \r\n    TABLE = dbo.SupportTicketEmbeddings, \r\n    COLUMN = IssueEmbedding, \r\n    SIMILAR_TO = @query_vector, \r\n    METRIC = 'cosine' \r\n) AS s \r\nJOIN dbo.SupportTickets t \r\n    ON s.TicketId = t.TicketId \r\nJOIN dbo.Customers c \r\n    ON t.CustomerId = c.CustomerId \r\nJOIN dbo.Products p \r\n    ON t.ProductId = p.ProductId \r\nWHERE t.TicketStatus = 'Open' \r\n  AND p.ProductName = @ProductArea \r\n  AND c.TenantId = @TenantId \r\nORDER BY s.distance; <\/code><\/pre>\n
The application expresses a straightforward requirement. The database faces a more complex optimization problem: find semantically relevant records, apply relational predicates, and return the requested qualifying results while minimizing unnecessary vector exploration and distance calculations.<\/p>\n
When vector retrieval lives outside the <\/span>database<\/span><\/span>\u00a0<\/span><\/h3>\n
The challenge becomes more apparent, when vector retrieval happens in a separate vector database, applications commonly split the workflow into multiple steps.<\/p>\n
    \n
  1. Send the query embedding to the external vector database.<\/li>\n
  2. Retrieve a fixed set of candidate IDs, for example, the top 100 nearest matches.<\/li>\n
  3. Send those candidate IDs to the relational database.<\/li>\n
  4. Apply filters, joins, permissions, and business rules in SQL.<\/li>\n
  5. Request more vector candidates and repeat the relational query if too few rows qualify.<\/li>\n<\/ol>\n
    \"Diagram<\/a>
    Diagram of vector retrieval across separate systems.<\/figcaption><\/figure><\/p>\n
    The vector database knows which embeddings are similar, but it may not have visibility into all the relational predicates, joins, security rules, and live business data evaluated by SQL. The relational database understands those constraints, but it receives only the candidate set selected by the external vector system.\nThe two systems optimize different parts of the request independently. This increases application complexity and data movement, while placing the burden of choosing candidate counts, coordinating retries, and reconciling data and security boundaries on the developer.<\/p>\n
    Vector Retrieval as a Part of SQL Query Processing<\/h3>\n
    The challenge is not simply where vectors are stored. The challenge is how vector retrieval participates in the execution of the complete query.<\/p>\n
    In Azure SQL, vectors are not stored as opaque blobs or managed through an external service bolted onto the database.<\/strong><\/p>\n
    SQL query optimizers have benefited from decades of investment in cardinality estimation and cost-based plan selection.<\/p>\n
    Because vector search is fully composable with relational operators such as filters, joins, aggregates, and security predicates, the optimizer can cost it in the context of the complete query. It can then compare alternative execution strategies and choose an efficient plan for a query containing vector search, rather than optimizing vector retrieval as an isolated step. DiskANN provides efficient candidate generation over large vector collections, while the SQL optimizer integrates that retrieval with the rest of the query.<\/p>\n
    \"Vector<\/a>
    Vector search becomes part of the same optimization framework as relational processing<\/figcaption><\/figure><\/p>\n
    <\/h3>\n
    How Azure SQL evolved beyond post-filtering<\/span><\/h3>\n
    Earlier DiskANN-based vector retrieval in Azure SQL followed a post-filtering model. The vector index first returned a candidate set, and relational predicates were applied afterward.<\/p>\n
    This works well when predicates are broad and most candidates qualify. As filters become more selective, however, many candidates may be discarded.<\/p>\n
    Azure SQL has moved beyond this model. The latest DiskANN Vector Index improvements<\/a> introduced iterative filtering, where predicates are applied during vector search rather than only after a fixed candidate set is generated. T<\/p>\n
    Here\u2019s a quick side-by-side comparison:<\/p>\n\n\n\n\n\n\n\n\n\n
    Before (Post-Filtering)<\/strong><\/td>\nNew Version (Iterative Filtering)<\/strong><\/td>\n<\/tr>\n<\/thead>\n
    The Problem:<\/strong>\u00a0Filtering happened\u00a0after<\/em>\u00a0retrieving vectors<\/td>\nThe Solution:<\/strong>\u00a0Filtering happens\u00a0during<\/em>\u00a0the search<\/td>\n<\/tr>\n
    You had to over-fetch and hope enough matched<\/td>\nThe search can continue until the requested number of qualifying results is found, when enough matching rows exist.<\/td>\n<\/tr>\n
     <\/p>\n
    SELECT TOP (20) t.id, t.title, s.distance<\/p>\n
    FROM VECTOR_SEARCH(<\/p>\n
    TABLE = wikipedia_articles,<\/p>\n
    COLUMN = title_vector,<\/p>\n
    SIMILAR_TO = @query_vector,<\/p>\n
    METRIC = ‘cosine’,<\/p>\n
    TOP_N = 20\u00a0 \/* Over-fetch *\/<\/p>\n
    ) AS s<\/p>\n
    WHERE category = ‘Technology’<\/p>\n
    ORDER BY s.distance;<\/td>\n
    		 <\/p>\n
    SELECT TOP (10) WITH APPROXIMATE<\/p>\n
    t.id, t.title, s.distance<\/p>\n
    FROM VECTOR_SEARCH(<\/p>\n
    TABLE = wikipedia_articles AS t,<\/p>\n
    COLUMN = title_vector,<\/p>\n
    SIMILAR_TO = @query_vector,<\/p>\n
    METRIC = ‘cosine’<\/p>\n
    ) AS s<\/p>\n
    WHERE t.category = ‘Technology’<\/p>\n
    ORDER BY s.distance;<\/td>\n<\/tr>\n
    Result:<\/strong>\u00a0Maybe 10 results, maybe 3, maybe 0<\/td>\nResult:<\/strong> Up to 10 qualifying Technology articles, when enough matching rows exist.<\/td>\n<\/tr>\n
    Had to guess how many to fetch (TOP_N = 20? 100?)<\/td>\nNo longer need to select a fixed over-fetch value<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Candidate exploration and relational qualification now work together. The engine no longer has to commit to a single fixed candidate count before it knows how the rest of the query will behave.<\/p>\n
     <\/p>\n
    One query, more intelligent choices<\/h2>\n
    WITH APPROXIMATE <\/strong>tells Azure SQL that approximate nearest-neighbor results are acceptable while leaving the execution strategy to the engine.<\/p>\n
    At compile<\/strong> time, the optimizer evaluates available approaches using the complete query context and estimated execution cost. Approximate nearest-neighbour(ANN) search may work well for large vector collections when predicates are not highly selective. Queries containing highly selective filters may benefit from exact nearest-neighbor search instead of additional approximate nearest-neighbor (ANN) exploration. The exact choice is workload-dependent and may consider factors such as predicate selectivity, data distribution, available relational and vector indexes, and the requested result count. These examples are illustrative and not a guaranteed decision matrix.<\/p>\n
    At runtime<\/strong>, the execution engine can adapt to available resources and query conditions. When additional qualifying rows are needed to satisfy a filtered Top-K request, Azure SQL can switch between approximate nearest-neighbor (ANN) search and exact nearest-neighbor(KNN) search as appropriate. This helps return qualifying results without requiring the application to guess an over-fetch value.<\/p>\n
    The same T-SQL surface can therefore support different execution strategies based on the query and available resources. Developers do not need to choose between\u00a0 ANN search and exact nearest-neighbor search. They express the retrieval intent, and Azure SQL determines how best to execute the query.<\/p>\n
    Bringing the power of SQL to AI retrieval<\/h2>\n
    Every database can add a vector index. The larger challenge is integrating vector retrieval into the optimizer, execution engine, security model, and query-processing infrastructure that production workloads already depend on.<\/p>\n
    By treating vectors as a first-class SQL data type, Azure SQL allows semantic retrieval to participate in decades of database innovation:<\/p>\n