Hybrid search with full-text and vectors

The first week of testing showed both kinds of search failing in opposite places. Vector search found the anti-fog film for a question about a steamy mirror, but ranked a generic paint above the exact product when a contractor typed PNT-114. Full-text search found PNT-114 instantly but returned nothing for something to stop my deck going grey. Hana's fix, which became the pilot design, was to run both searches and merge their results, an approach called hybrid search.

Full-text search in brief

Full-text search indexes the words in text columns so queries can find rows containing words, phrases, prefixes or forms of a word, and rank them by relevance. It needs a full-text catalog, a full-text index on the table that points at a unique single-column key index, and a language for word breaking and stemming. In Azure SQL Database the full-text index is populated automatically as rows change, using change tracking.

CREATE FULLTEXT CATALOG ftc_catalog AS DEFAULT;

CREATE FULLTEXT INDEX ON catalog.Product
    (ProductName LANGUAGE 1033, ShortDescription LANGUAGE 1033, UsageNotes LANGUAGE 1033, Sku LANGUAGE 1033)
KEY INDEX PK_Product
WITH CHANGE_TRACKING AUTO;

Queries use predicates in a WHERE clause, CONTAINS and FREETEXT, or table-valued functions that also return a rank, CONTAINSTABLE and FREETEXTTABLE. CONTAINS and CONTAINSTABLE take precise conditions: exact words, phrases in quotes, prefixes with an asterisk, inflectional forms with FORMSOF, and proximity with NEAR. FREETEXT and FREETEXTTABLE take ordinary text, break it into words, stem them and match any of them, ranking rows by relevance; Microsoft's August 2026 hybrid search guidance describes that ranking as based on BM25, a standard relevance formula. For hybrid search, the ranked table-valued functions are what matter, because fusion needs ranks.

SELECT TOP (20) p.ProductId, p.ProductName, ft.[RANK]
FROM FREETEXTTABLE(catalog.Product, (ProductName, ShortDescription, UsageNotes, Sku),
                   N'PNT-114 interior eggshell') AS ft
JOIN catalog.Product AS p ON p.ProductId = ft.[KEY]
ORDER BY ft.[RANK] DESC;

Why the two lists cannot simply be added

The obvious way to merge results is to add the scores, but the scores are on unrelated scales. Full-text RANK values run from 0 to 1,000 and mean nothing outside their own query. Cosine distance runs from 0 to 2 and smaller is better. Normalizing both to 0 to 1 helps a little, but the shape of the scores differs from query to query, so a fixed weighting rewards whichever search happens to produce more spread-out scores. What both lists do share is order: each has a first, second and third result.

Reciprocal rank fusion

Reciprocal rank fusion, RRF, merges ranked lists using only positions. Each result gets, from each list it appears in, a score of one divided by a constant plus its rank, and the scores are added. A result near the top of both lists scores highest; a result at the top of one list and missing from the other still scores well; results far down both lists score little. The constant, usually 60, damps the difference between the very top ranks so that one list's first place does not dominate. The DP-800 outline names RRF explicitly.

A weight on each list shifts the balance when one search is more trustworthy for a kind of query. Hana kept equal weights for the pilot but noted that queries containing something that looks like a SKU could give full-text a higher weight.

def rrf(lists, k=60, weights=None):
    weights = weights or [1.0] * len(lists)
    scores = {}
    for w, ranked in zip(weights, lists):
        for rank, item in enumerate(ranked, start=1):
            scores[item] = scores.get(item, 0.0) + w / (k + rank)
    return sorted(scores.items(), key=lambda kv: kv[1], reverse=True)

# Query: "low-VOC paint for a nursery"
fulltext = ["PNT-205 low-VOC eggshell", "PNT-114 interior eggshell", "PRM-020 primer", "PNT-301 ceiling paint"]
vector   = ["PNT-205 low-VOC eggshell", "PNT-410 zero-VOC kids room paint", "PNT-114 interior eggshell",
            "AIR-005 air purifier"]

for item, score in rrf([fulltext, vector])[:5]:
    print(f"{score:.5f}  {item}")

0.03279  PNT-205 low-VOC eggshell
0.03200  PNT-114 interior eggshell
0.01613  PNT-410 zero-VOC kids room paint
0.01587  PRM-020 primer
0.01562  PNT-301 ceiling paint

The result illustrates why RRF works well with little tuning. Products that both searches agree on rise. A product only vector search found, such as the zero-VOC paint described as made for children's rooms without the word nursery, still makes the top three. And the air purifier, a weak vector match only, sinks.

Hybrid search in T-SQL

In T-SQL, hybrid search is two ranked queries and a fusion step. Each query produces up to a few dozen candidates with a rank number from ROW_NUMBER, a full outer join brings the two lists together, and the RRF formula computes the fused score. Hana wrapped it in a stored procedure so the application sends only the question.

CREATE OR ALTER PROCEDURE askfinch.HybridProductSearch
    @question nvarchar(400), @top int = 10, @k int = 60
WITH EXECUTE AS 'ai_proc_owner'
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @q vector(1536) = AI_GENERATE_EMBEDDINGS(@question USE MODEL CatalogEmbedder);

    WITH ft AS (
        SELECT TOP (50) [KEY] AS ProductId, ROW_NUMBER() OVER (ORDER BY [RANK] DESC) AS rnk
        FROM FREETEXTTABLE(catalog.Product, (ProductName, ShortDescription, UsageNotes, Sku), @question)
        ORDER BY [RANK] DESC),
    vs AS (
        SELECT TOP (50) e.ProductId,
               ROW_NUMBER() OVER (ORDER BY VECTOR_DISTANCE('cosine', e.Embedding, @q)) AS rnk
        FROM catalog.ProductEmbedding AS e
        ORDER BY VECTOR_DISTANCE('cosine', e.Embedding, @q))
    SELECT TOP (@top) COALESCE(ft.ProductId, vs.ProductId) AS ProductId, p.ProductName,
           COALESCE(1.0 / (@k + ft.rnk), 0) + COALESCE(1.0 / (@k + vs.rnk), 0) AS rrf_score
    FROM ft FULL OUTER JOIN vs ON vs.ProductId = ft.ProductId
    JOIN catalog.Product AS p ON p.ProductId = COALESCE(ft.ProductId, vs.ProductId)
    WHERE p.IsActive = 1
    ORDER BY rrf_score DESC;
END;

For the review data, the vector branch would use VECTOR_SEARCH with SELECT TOP (50) WITH APPROXIMATE. Because the approximate query must order only by distance, the ranking with ROW_NUMBER goes in an outer query around it. The same pattern searches guide chunks; the answer procedure in lesson 8 runs both and keeps the best passages.

Another meaning of hybrid

The word hybrid is also used for a different idea: combining a vector search with ordinary filters, such as only in-stock products at Store 12, or only reviews in English. The latest vector index version applies such filters during the search, as lesson 6 explained, and Microsoft's documentation sometimes calls that hybrid too. On the exam, read the scenario: merging keyword and vector rankings is RRF hybrid search; restricting a vector search by column values is filtered vector search.

Measuring whether hybrid helped

Hana measured each approach on the 60 test questions with a simple measure: was a product the store managers had marked as correct in the top three results? She also looked at the questions each method missed, which was more instructive than the totals.

Hybrid search beat both. The five remaining misses pointed to data, not search: three products had one-line descriptions that gave neither search anything to match, so Grace's team rewrote them. Nikolai's question applied here too: the people reading search results scan a short list while a customer waits, so a correct product in the top three matters more than a perfect order below it, and the test measured exactly that.

Tuning the fusion

RRF has few settings, which is part of its appeal, but three choices still matter. The first is how many candidates each search contributes. Hana took the top 50 from each; taking only 10 meant an item ranked 12th by one search and 3rd by the other could be missed, while taking 500 added work without changing the top ten. The second is the constant k. Lower values, such as 20, make first places count more; higher values flatten the list. On the test questions, k between 40 and 80 gave the same top three for almost every question, so the common default of 60 stayed. The third is the weights. A query that looks like a SKU, matched by a simple pattern such as three letters, a hyphen and three digits, is sent to full-text with a weight of 2 and to vector search with 1, which put the exact product first in every SKU test.

Full-text search has its own tuning. Stopwords, such as the and for, are ignored by default, which is usually right. The language of the index matters for stemming, so that sanding finds sand and sander; Harrow & Finch's index uses English, and French product names, which some Canadian suppliers use, rely on vector search to match. A thesaurus file can map trade words to everyday ones, but the team found vector search already covered most synonyms, and kept the thesaurus for brand abbreviations only.

Finally, hybrid search should be evaluated as a whole, not by its parts. A change that improves vector search alone can make hybrid results worse if it pushes good full-text matches down, so every change was tested against the same 60 questions with the top-three measure before it was kept.

Hybrid search also has a cost that pure vector search does not: two indexes to maintain and two queries per request. On the catalogue, the full-text branch averaged 20 milliseconds and the vector branch 150, run in parallel, so the extra branch added almost nothing to response time. The full-text index used about 60 MB, small next to the vectors. Where hybrid search becomes expensive is on very large text collections, where the full-text index is large and its population after bulk loads takes time; for those, the team planned to populate during the nightly window and monitor population status with the FULLTEXTCATALOGPROPERTY function, so a half-populated index is never mistaken for missing data.