3.5 — Implement Hybrid Search with Reciprocal Rank Fusion, and Evaluate It
DP-800 Domain 3 · Skill 3.5
What You Need to Know
Hybrid search runs a keyword search (full-text) and a semantic search (vector) for the same query, then merges the two ranked lists. Reciprocal rank fusion (RRF) is the standard way to merge them, because it only uses ranks, which avoids comparing incompatible scores.
Why ranks, not scores
A full-text RANK (for example 0–1000) and a cosine distance (0–2, lower is better) aren't comparable. RRF ignores the raw values and rewards documents that appear near the top of either list:
RRF score(d) = Σ over each list of 1 / (k + rank of d in that list), with k usually 60.
A document ranked 1st in one list and 3rd in the other beats one ranked 1st in just one list.
Implementing it in T-SQL
DECLARE @q nvarchar(400) = N'waterproof hiking boots for wide feet';
DECLARE @qv vector(1536) = AI_GENERATE_EMBEDDINGS(@q USE MODEL ProductEmbeddings);
DECLARE @k int = 60;
WITH keyword AS (
SELECT TOP (50) ft.[KEY] AS ProductID,
ROW_NUMBER() OVER (ORDER BY ft.RANK DESC) AS rnk
FROM FREETEXTTABLE(dbo.Product, Description, @q) AS ft
ORDER BY ft.RANK DESC
),
semantic AS (
SELECT TOP (50) v.ProductID,
ROW_NUMBER() OVER (ORDER BY VECTOR_DISTANCE('cosine', v.Embedding, @qv)) AS rnk
FROM dbo.ProductVector AS v
ORDER BY VECTOR_DISTANCE('cosine', v.Embedding, @qv)
)
SELECT TOP (10) COALESCE(k.ProductID, s.ProductID) AS ProductID,
ISNULL(1.0 / (@k + k.rnk), 0) + ISNULL(1.0 / (@k + s.rnk), 0) AS rrf_score
FROM keyword AS k
FULL OUTER JOIN semantic AS s ON s.ProductID = k.ProductID
ORDER BY rrf_score DESC;
- Take the top N from each method (for example 50), assign ranks with
ROW_NUMBER, FULL OUTER JOIN on the key, and sum the reciprocal ranks. A document missing from one list contributes 0 from that list. - For large tables, replace the exact semantic CTE with
VECTOR_SEARCH ... TOP (50) WITH APPROXIMATE. - Apply business filters (in stock, region) in both branches.
- Weights can tilt the fusion toward one side (w × 1/(k + rank)) when one source is more reliable for the domain.
Evaluating vector and hybrid search
| What to measure | How |
|---|---|
| Relevance | A labeled test set of queries with known relevant results. Measure precision@k, recall@k, MRR or nDCG for keyword-only, vector-only and hybrid search |
| ANN quality | Recall of the approximate results compared with exact (ENN) results on the same queries. It should be close to 1 |
| Latency and cost | Query duration (Query Store, SET STATISTICS TIME), embedding-call latency, CPU and I/O |
| Tuning levers | Chunk size and overlap, embedding model and dimensions, candidate-list size, RRF k, filters, vector index on or off |
Compare the variants on the same test set before you change production. A higher similarity score on one example isn't evidence.
Key Concept: Hybrid = full-text ranks + vector ranks → RRF (1/(k + rank), k ≈ 60) → the top results. Measure relevance against a labeled set, and measure ANN recall against exact search.
Exam Traps
- Adding a full-text RANK to a cosine distance directly (incompatible scales; use ranks)
- Using an INNER JOIN between the two result lists, which drops documents found by only one method
- Judging search quality from a single query instead of a labeled evaluation set
- Assuming a vector index returns the same results as exact search without measuring recall
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 14 DP-800 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Vector and hybrid search with RRF (SQL Server provider), Microsoft Learn
- Implement AI capabilities in database solutions (learning path), Microsoft Learn
- Vector search and vector indexes in the SQL Database Engine, Microsoft Learn
- FREETEXTTABLE (Transact-SQL), Microsoft Learn
- Relevance scoring in hybrid search using RRF, Microsoft Learn