Hybrid search is usually described as combining keyword search with vector search. Microsoft SQL does that, for sure, but we do quite a bit more right out of the box. SQL dynamically evaluates queries and switches between kNN and ANN vector search based on cost and selectivity. These two types of hybrid search, work together, ensuring your semantic queries return the best possible results to your apps and agents.
Text search
In 1998, Microsoft Research Cambridge welcomed Stephen Robertson, one of the principal architects of BM25, or Best Matching 25, the relevance-ranking algorithm that became foundational to modern information retrieval. Robertson spent the next 15 years at Microsoft Research, and SQL Server embraced full-text search early. Today, FREETEXTTABLE() still uses BM25 to rank results by relevance. Vector search may feel new, but relevance-ranked search has been part of the SQL story for decades.
The magic of FREETEXTTABLE()
Unlike Microsoft SQL’s `CONTAINSTABLE()`, which searches text using precise terms, phrases, prefixes, proximity, and explicit inflectional forms, `FREETEXTTABLE()` is designed for broad, relevance-ranked retrieval.
It automatically applies language-aware word breaking and stemming to expand user input across related word forms. The user queries for “run,” but the engine understands “ran,” “running,” and “runs” without any extra clarification. This makes text search in SQL especially useful when users describe what they want rather than knowing the exact words stored in the data.
Sample syntax
SELECT p.Name, ft.RANK
FROM FREETEXTTABLE(Product, (Description), 'running shoes') ft
JOIN Product p ON p.Id = ft.[KEY]
ORDER BY ft.RANK DESC;Vector search
In 2025, SQL embraced DiskANN, another innovation born in Microsoft Research. First published in 2019, DiskANN showed that approximate vector search could remain fast and accurate across millions, even billions, of vectors without requiring enormous amounts of memory. The industry noticed, with other databases implementing DiskANN-inspired approaches of their own. Today, DiskANN is part and parcel of SQL’s approach to vector search, bringing that same research directly into the engine.
kNN, or k-nearest neighbors, is the exact form of vector search. It compares the query vector against the available vectors and returns the closest matches according to the selected distance metric. It is simple and accurate, but the work grows with the size of the data set, which is why ANN becomes more useful at larger scale.
The magic of VECTOR_SEARCH()
Unlike Microsoft SQL’sVECTOR_DISTANCE(), which performs an exact comparison and never uses a vector index, VECTOR_SEARCH() is designed to retrieve the nearest matches efficiently across large vector sets. Predicates can even participate during the search through iterative filtering, helping SQL return the requested number of relevant rows without oversampling.Sample syntax
SELECT TOP (10) WITH APPROXIMATE
p.Id, p.Name, v.distance
FROM VECTOR_SEARCH(
TABLE = ProductEmbedding AS p,
COLUMN = Embedding,
SIMILAR_TO = @Embedding,
METRIC = 'cosine'
) AS v
ORDER BY v.distance;Model integration
In 2022, Azure SQL introduced sp_invoke_external_rest_endpoint, giving T-SQL a native way to call REST services directly from the database. Today, that capability underpins SQL’s growing model integration story, including External Models and built-in AI functions like AI_GENERATE_EMBEDDINGS that simplify integration. And when these built-in abstractions do not yet cover emerging interactions and models, the direct REST endpoint remains available, making scenarios such as GPT-based intent detection or Cohere reranking part of the same SQL-centered RAG pipeline.
Endpoints in Azure SQL database
sp_invoke_external_rest_endpoint is implemented a little differently in Azure SQL than in SQL Server. In both, credentials remain protected inside SQL and endpoint access requires explicit permissions. Azure SQL adds another layer of protection with an allowlist of approved service domains, including most major Azure services, Microsoft Graph, Power BI, and – perhaps most important here – Microsoft Foundry. Administrators can validate supported endpoints and use services such as API Management when they need to extend access beyond the built-in list.Sample syntax
DECLARE @Response NVARCHAR(MAX);
EXEC sys.sp_invoke_external_rest_endpoint
@method = 'POST',
@url = 'https://example.com/api',
@payload = N'{"query":"running shoes"}',
@response = @Response OUTPUT;Hybrid search
Now we bring them together. Full-text search finds the words that matter, while vector search finds the meaning behind them. Each produces its own ranked results, and Reciprocal Rank Fusion (RRF) combines those rankings into a single result set, optionally weighting one search more heavily than the other.
A typical workflow
Depending on your needs, a hybrid search flow can include several curation steps to help ensure user input is tuned for optimal search and fused results are ranked for optimal ordering. Every step in a comprehensive hybrid search flow can be orchestrated in SQL, helping enable apps without introducing unnecessary change or complexity.
Here’s an example:
This is where SQL becomes especially useful for RAG. Query rewriting and intent detection can improve the searches before they run, RRF can fuse the results, and reranking can refine the final order. The entire retrieval pipeline can stay close to the production data your apps and agents already depend on.
J’aime beaucoup ce type d’article qui explique bien les concepts et leur histoire