In today’s data-driven world, delivering precise and contextually relevant search results is critical. SQL Server and Azure SQL Database now enable this through Hybrid Search\u2014a technique that combines traditional full-text search with modern vector similarity search. This allows developers to build intelligent, AI-powered search experiences directly inside the database engine.<\/p>\n
Funny enough one of the key algorithms needed for implementing Hybrid Search, BM25<\/a>, has been included in SQL Server is almost forever<\/a>, without much fanfare: RAG pattern and similarity search is bringing it to the light.<\/p>\n Hybrid Search blends two key search paradigms:<\/p>\n Full-Text Search (FTS): Uses the Vector Search: Introduced via the new By combining both, you can return results that are relevant not only because of exact term matches but also because of their underlying meaning.<\/p>\n Once you’ve run both search strategies, you can improve the final result list by applying Reciprocal Rank Fusion (RRF)\u2014a simple, effective method for combining ranked lists from different sources.<\/p>\n While SQL Server and Azure SQL don’t offer a native Where rank is the position of the item in its respective list, and This makes it easy to write RRF logic directly in SQL, without needing any custom functions or extensions. You can assign RRF scores to each document using a SELECT statement, then sort by the final combined score.<\/p>\n You can find a working example of Hybrid Search with manual RRF in the Azure SQL + OpenAI samples repo<\/a>. Here’s a simplified version of the process:<\/p>\n This approach gives more weight to results that rank highly in either method, ensuring that strong keyword matches or highly semantically similar items surface at the top.<\/p>\n By combining full-text and vector-based search, and applying RRF for re-ranking, you’re able to build smarter and more flexible search solutions directly within SQL Server or Azure SQL. This approach is not only technically efficient but also developer-friendly and highly adaptable to real-world use cases.<\/p>\nWhat Is Hybrid Search?<\/h2>\n
\n
FREETEXTTABLE<\/code> function, which ranks results using the BM25 algorithm. This provides high-quality keyword-based relevance scoring.<\/p>\n<\/li>\nVECTOR<\/code> data type and VECTOR_DISTANCE()<\/code> function, vector search enables semantic matching of embeddings\u2014ideal for scenarios involving natural language queries.<\/p>\n<\/li>\n<\/ul>\nRe-Ranking with RRF<\/h2>\n
RRF()<\/code> function, RRF is straightforward to implement manually with basic SQL logic. The formula is simple:<\/p>\n1 \/ (rank + k)<\/code><\/pre>\n<\/div>\n<\/div>\nk<\/code> is a constant (commonly 60, though it can be tuned for your scenario, I like to set it equal to the k<\/code> of requested results)<\/p>\nSample Implementation<\/h2>\n
-- Keyword (Fulltext) search\r\nSELECT TOP(@k)\r\n id, \r\n RANK() OVER (ORDER BY ft_rank DESC) AS rank,\r\nINTO\r\n #keyword_search\r\nFROM\r\n (\r\n SELECT TOP(@k)\r\n id,\r\n ftt.[RANK] AS ft_rank,\r\n FROM \r\n dbo.wikipedia_articles_embeddings w\r\n INNER JOIN \r\n FREETEXTTABLE(dbo.wikipedia_articles_embeddings, *, @q) AS ftt ON w.id = ftt.[KEY]\r\n ORDER BY\r\n ft_rank DESC\r\n ) AS freetext_documents\r\nORDER BY\r\nrank ASC\r\n\r\n-- Semantic search\r\nSELECT TOP(@k)\r\n id, \r\n RANK() OVER (ORDER BY cosine_distance) AS rank\r\nINTO\r\n #semantic_search\r\nFROM (\r\n SELECT TOP(@k)\r\n id, \r\n VECTOR_DISTANCE('cosine', @e, content_vector_ada2) AS cosine_distance\r\n FROM \r\n dbo.wikipedia_articles_embeddings w\r\n ORDER BY\r\n cosine_distance\r\n) AS similar_documents\r\n\r\n-- Reciprocal Rank Fusion (RRF)\r\nSELECT TOP(@k)\r\n COALESCE(ss.id, ks.id) AS id,\r\n ss.rank AS semantic_rank,\r\n ks.rank AS keyword_rank,\r\n COALESCE(1.0 \/ (@k + ss.rank), 0.0) +\r\n COALESCE(1.0 \/ (@k + ks.rank), 0.0) AS score \r\nFROM\r\n #semantic_search ss\r\nFULL OUTER JOIN\r\n #keyword_search ks ON ss.id = ks.id\r\nORDER BY \r\n score DESC<\/code><\/pre>\nPractical Benefits of Hybrid Search + RRF<\/h2>\n