Hybrid search runs a keyword query and a vector query over the same rows and merges the two ranked lists. Because the two scores live on incompatible scales, the merge is usually done by position with reciprocal rank fusion.
Why scores can’t simply be added
CONTAINSTABLE and FREETEXTTABLE return RANK from 0 to 1000 where higher is better, while vector distances are small numbers where lower is better. Adding or averaging them mixes units and directions. Reciprocal rank fusion (RRF) ignores raw scores and uses each document’s position: every list contributes 1 / (k + rank), the contributions are summed per document, and the sum is sorted descending. Documents near the top of both lists win. Azure AI Search documents that RRF performs best with a small constant such as k = 60, and that this k is unrelated to the number of nearest neighbours. Weighting is applied by multiplying a list’s contribution. Figure 14.1 shows how the two ranked lists are merged.
RRF in T-SQL
DECLARE @q NVARCHAR(400) = N'waterproof hiking boots';
DECLARE @qv VECTOR(1536) = AI_GENERATE_EMBEDDINGS(@q USE MODEL EmbeddingModel);
WITH kw AS (
SELECT ft.[KEY] AS product_id,
ROW_NUMBER() OVER (ORDER BY ft.[RANK] DESC) AS rnk
FROM FREETEXTTABLE(dbo.Products, description, @q) AS ft
),
vec AS (
SELECT v.product_id, ROW_NUMBER() OVER (ORDER BY v.distance) AS rnk
FROM (SELECT TOP (50) WITH APPROXIMATE p.product_id, r.distance
FROM VECTOR_SEARCH(TABLE = dbo.Products AS p, COLUMN = embedding,
SIMILAR_TO = @qv, METRIC = 'cosine') AS r
ORDER BY r.distance) AS v
)
SELECT TOP (10)
COALESCE(kw.product_id, vec.product_id) AS product_id,
ISNULL(1.0 / (60 + kw.rnk), 0) + ISNULL(1.0 / (60 + vec.rnk), 0) AS rrf_score
FROM kw
FULL OUTER JOIN vec ON kw.product_id = vec.product_id
ORDER BY rrf_score DESC;The keyword list is ranked by full-text RANK, the vector list by ascending distance (the approximate search sits in a subquery because ROW_NUMBER needs the subquery pattern), and a full outer join keeps products found by only one method, which contribute zero from the other list. To favour semantics, multiply the vector term (for example by 2); to favour exact terms, weight the keyword term.
Other re-ranking options
The SQL vector FAQ describes re-ranking by combining vector search with full-text search (which provides BM25-style ranking), by additional SQL business logic, or by a semantic re-ranking model (a cross-encoder) that scores each candidate passage against the query. Filters such as stock status or region belong in the WHERE clause of each branch so that both lists rank only eligible rows.
Common trap: Normalising nothing and adding
RANKtodistanceto build a hybrid score - full-text rank is 0-1000 higher-is-better and vector distance is lower-is-better; merge by position with RRF (1/(k + rank),ktypically 60) or normalise both before weighting.