Launch Deal · 50% off 1st month ·

3.3 — Choose Full-Text, Semantic Vector or Hybrid Search, and Implement Full-Text Search

DP-800 Domain 3 · Skill 3.3

What You Need to Know

"Intelligent search" isn't always vector search. Choose the technique from how users search and what must match.

Choosing

Technique Matches Strengths Weaknesses Choose it when
Full-text search Words, with inflections, prefixes, proximity and a thesaurus Exact terms, product codes, names, Boolean logic; no AI model needed Misses synonyms and paraphrases ("laptop" vs "notebook computer") Users type known keywords, IDs or exact phrases
Semantic vector search Meaning, through embeddings Natural-language questions, synonyms, cross-language (with a multilingual model) Can miss exact codes and rare terms; needs a model and vectors Users describe what they need in their own words
Hybrid search Both, with the result lists merged (for example by RRF) The best overall relevance for mixed queries More moving parts Real-world search boxes that get both "error 0x80070005" and "my laptop won't update"

Implementing full-text search

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.Article (Title LANGUAGE 1033, Body LANGUAGE 1033)
    KEY INDEX PK_Article ON ftCatalog WITH CHANGE_TRACKING AUTO;
Predicate or function Use
CONTAINS(Body, '"solar panel" AND NOT lease') Precise word and phrase logic, prefixes ("inverter*"), NEAR, inflectional forms (FORMSOF(INFLECTIONAL, run))
FREETEXT(Body, 'how to replace a battery') Looser matching on the meaning of words: stemming plus the thesaurus
CONTAINSTABLE / FREETEXTTABLE Return a RANK per row, which you need for sorting and for hybrid fusion
  • A full-text index needs a unique, single-column, non-nullable key index.
  • CHANGE_TRACKING AUTO keeps the index updated as rows change.
  • Choose the right language per column for word breaking and stemming. Stopwords (and/the/of) are ignored.

Semantic vector search, in brief

  • Embed the query with the same model as the content, then rank by VECTOR_DISTANCE (exact) or VECTOR_SEARCH (approximate, with an index). Details are in lesson 3.4.
  • Combine it with relational filters (category, price range, in stock) for precise results.

Key Concept: Keywords and codes → full-text (CONTAINS / CONTAINSTABLE). Natural-language meaning → vector. Both, which is what most search boxes need → hybrid: rank with CONTAINSTABLE and VECTOR_SEARCH, then fuse the lists.

Exam Traps

  • Using vector search for exact part numbers such as "XR-2207-B" (embeddings blur exact tokens)
  • Using LIKE '%term%' on large text columns instead of full-text search
  • Sorting full-text results without CONTAINSTABLE or FREETEXTTABLE (only these return a rank)
  • Creating a full-text index without a unique key index

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.

See plans

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.