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 AUTOkeeps 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) orVECTOR_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
CONTAINSTABLEorFREETEXTTABLE(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.
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
- Full-text search, Microsoft Learn
- Query with full-text search, Microsoft Learn
- CONTAINSTABLE (Transact-SQL), Microsoft Learn
- Vector search and vector indexes in the SQL Database Engine, Microsoft Learn