Choose Your Search Strategy

Three ways to search your data

The search strategy decision

Three search strategies — choose based on your query patterns

Feature	Full-Text Search	Vector Search	Hybrid Search
Matches on	Exact words and word forms (stemming)	Semantic meaning (embeddings)	Both keywords AND meaning
Query example	CONTAINS(Description, 'wireless mouse')	VECTOR_SEARCH(embedding, @queryVector, 10)	Full-text + vector scores fused with RRF
Finds synonyms?	Only with thesaurus config	Yes — automatically	Yes
Handles typos?	No (exact match)	Somewhat (embedding similarity)	Better than either alone
Speed	Very fast (inverted index)	Fast (vector index, ANN)	Moderate (two indexes)
Setup complexity	Low — full-text catalog + index	Medium — embeddings + vector index	High — both systems + fusion logic
Best for	Exact keyword search, known terminology	Natural language queries, semantic similarity	Production search systems needing both precision and recall

Decision guide

Scenario	Best Strategy	Why
Users search by product SKU or exact name	Full-text	Exact keyword matching is fastest and most precise
Users describe what they want in natural language	Vector	Semantic understanding matches intent, not just words
E-commerce search with millions of products	Hybrid	Combines keyword precision with semantic recall
Log analysis searching for error codes	Full-text	Exact pattern matching, no semantic interpretation needed
Customer support finding similar past tickets	Vector	Tickets with the same issue may use completely different words
Legal document discovery	Hybrid	Need both exact legal terms AND conceptually similar clauses

Implementing full-text search

Full-text search uses an inverted index — a data structure that maps words to the rows containing them.

Setup

-- Step 1: Create a full-text catalog
CREATE FULLTEXT CATALOG ProductCatalog AS DEFAULT;

-- Step 2: Create a full-text index on the table
CREATE FULLTEXT INDEX ON Products (
    Name LANGUAGE 1033,           -- English
    Description LANGUAGE 1033
)
KEY INDEX PK_Products              -- Must reference a unique index
ON ProductCatalog
WITH (CHANGE_TRACKING AUTO);       -- Auto-update when data changes

Querying with CONTAINS and FREETEXT

-- CONTAINS: precise keyword matching with Boolean operators
SELECT Name, Description
FROM Products
WHERE CONTAINS(Description, '"wireless mouse" AND bluetooth');

-- CONTAINS with proximity
SELECT Name FROM Products
WHERE CONTAINS(Description, 'NEAR((battery, life), 5)');

-- FREETEXT: natural language (less precise, broader results)
SELECT Name, Description
FROM Products
WHERE FREETEXT(Description, 'lightweight portable laptop for travel');

Ranking with CONTAINSTABLE and FREETEXTTABLE

-- Get ranked results with relevance scores
SELECT p.Name, ft.RANK
FROM Products p
INNER JOIN CONTAINSTABLE(Products, Description, '"wireless" OR "bluetooth"') ft
    ON p.ProductId = ft.[KEY]
ORDER BY ft.RANK DESC;

💡 Exam tip: CONTAINS vs FREETEXT

CONTAINS = precise, supports Boolean operators (AND, OR, NOT, NEAR), wildcards, inflectional forms. Use when users know specific terms.

FREETEXT = natural language, automatically stems words and finds related terms. Use when users describe what they want conversationally.

The exam often presents a scenario where one is clearly better than the other based on query precision requirements.

💡 Scenario: Ingrid's document search at Nordic Shield

Nordic Shield Insurance has 3 million claims documents. Claim adjusters need two types of search:

1. Find all claims mentioning a specific policy number → Full-text search (exact term matching)
2. Find claims similar to the current claim → Vector search (semantic similarity)

Ingrid implements hybrid search — full-text for known identifiers, vector for conceptual similarity. When an adjuster searches “water damage kitchen renovation,” the system finds claims that match those keywords AND claims about “flood repair” or “plumbing leak restoration” that use different words but mean similar things.

Question

What is the main advantage of vector search over full-text search?

Click or press Enter to reveal answer

Answer

Vector search matches by semantic meaning, not exact words. A search for 'affordable laptop for students' can find products described as 'budget-friendly notebook for college' — even though they share no words in common. Full-text search would miss this because the keywords do not match.

Click to flip back

Question

What is a full-text catalog in SQL Server?

Click or press Enter to reveal answer

Answer

A full-text catalog is a logical container for full-text indexes. It organises indexes and manages the population (building) of the inverted index. Each database can have multiple catalogs, and each table's full-text index belongs to one catalog.

Click to flip back

Question

What is the difference between CONTAINS and FREETEXT?

Click or press Enter to reveal answer

Answer

CONTAINS provides precise keyword matching with Boolean operators (AND, OR, NOT, NEAR) and wildcard support. FREETEXT performs natural language matching — it automatically applies stemming and finds related terms. CONTAINS is for precision, FREETEXT is for recall.

Click to flip back

Knowledge Check

Leo at SearchWave is building product search. Users type queries like 'comfortable shoes for running on trails.' Many relevant products are described with different words like 'cushioned trail runners' or 'off-road athletic footwear.' Which search strategy should Leo implement?

AFull-text search with FREETEXT for natural language matchingBVector search to match by semantic similarityCFull-text search with CONTAINS and a thesaurus file mapping synonymsDHybrid search combining full-text and vector search

Check Answer

🎬 Video coming soon

Next up: Vector Data: Types, Indexes, and Storage — design your database for vector data with the right data types, indexes, and storage strategies.