This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Implement AI capabilities in database solutions (25–30%)
   --> Design and implement intelligent search
      --> Implement full-text search

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Full-text search is one of the foundational search technologies available in Microsoft SQL Server and Azure SQL Managed Instance. Unlike traditional SQL searches that rely on exact text matching through operators such as LIKE, full-text search provides a much more efficient and intelligent mechanism for searching large collections of textual data.

For the DP-800: Developing AI-Enabled Database Solutions exam, you should understand:

• What full-text search is
• When it should be used
• How it works internally
• Full-text indexes and catalogs
• Supported query predicates and functions
• Language-aware searching
• Stoplists and thesaurus files
• Ranking search results
• Performance considerations
• When to choose full-text search instead of vector or hybrid search

Although AI-powered semantic search is becoming increasingly popular, full-text search remains an important technology for applications that require fast keyword-based retrieval.

What Is Full-Text Search?

Full-text search is a SQL Server feature that enables efficient searching of large text columns.

Unlike:

WHERE Description LIKE '%backup%'

full-text search creates a specialized index that understands words rather than simple character sequences.

It supports searching within:

• CHAR
• VARCHAR
• NCHAR
• NVARCHAR
• TEXT (legacy)
• NTEXT (legacy)
• XML
• FILESTREAM documents through filters

Instead of scanning every row, SQL Server searches an optimized full-text index.

Why Traditional LIKE Queries Are Limited

Many developers initially use:

SELECT *
FROM Articles
WHERE Content LIKE '%security%'

Although this works, it has several disadvantages:

• Table scans on large datasets
• Poor performance
• Cannot rank results
• No language awareness
• No stemming
• No synonym support
• Limited search capabilities

For enterprise search applications, LIKE queries do not scale effectively.

Benefits of Full-Text Search

Full-text search provides:

• Fast keyword searches
• Phrase searching
• Prefix matching
• Inflectional searches
• Linguistic processing
• Word breaking
• Ranking of results
• Stop word removal
• Efficient indexing
• Large-scale text retrieval

Full-Text Search Architecture

Several components work together.

Source Tables

Contain text data.

Example:

Articles
Products
KnowledgeBase
SupportTickets
Policies

Full-Text Index

Instead of indexing every character, SQL Server stores:

• Tokens
• Word positions
• Language metadata

This dramatically speeds searches.

Full-Text Catalog

A full-text catalog is a logical container for one or more full-text indexes.

Modern SQL Server versions automatically manage catalogs, but understanding the concept remains important for the DP-800 exam.

Word Breakers

SQL Server separates text into words using language-specific rules.

Example:

SQL Server enables intelligent search.

becomes

SQL
Server
enables
intelligent
search

Different languages use different tokenization rules.

Stemmers

Stemmers recognize grammatical variations.

Searching:

run

may also find

• running
• runs
• ran

depending on the configured language.

Enabling Full-Text Search

Before using full-text search:

1. Install Full-Text Search feature.
2. Create a unique key index.
3. Create a full-text catalog (optional in newer versions).
4. Create a full-text index.

Example:

CREATE FULLTEXT INDEX
ON Articles(Content)
KEY INDEX PK_Articles;

The index is then populated.

Full-Text Predicates

The DP-800 exam expects familiarity with common predicates.

CONTAINS()

Searches for precise words or phrases.

Example:

SELECT *
FROM Articles
WHERE CONTAINS(Content,'Azure');

Phrase Search

CONTAINS(Content,'"Azure SQL"')

Returns only rows containing the complete phrase.

Boolean Operators

Supports:

AND
OR
AND NOT

Example:

CONTAINS(Content,'"Azure" AND "Backup"')

Prefix Search

CONTAINS(Content,'"cloud*"')

Matches

• cloud
• clouds
• cloud-based
• clouding

Proximity Search

Finds words located near each other.

Example:

database NEAR backup

Useful when context matters.

FREETEXT()

Unlike CONTAINS(), FREETEXT searches for the meaning of words rather than exact expressions.

Example:

SELECT *
FROM Articles
WHERE FREETEXT(Content,'database recovery');

SQL Server automatically considers:

• synonyms
• stemming
• inflectional forms

It is more natural-language oriented than CONTAINS().

Ranking Results

Often multiple documents match.

SQL Server can assign relevance rankings.

Functions include:

CONTAINSTABLE()
FREETEXTTABLE()

Example:

SELECT *
FROM CONTAINSTABLE
(
Articles,
Content,
'Azure'
)

Returns:

• KEY
• RANK

Applications can sort using the ranking score.

Stoplists

Certain words appear so frequently that indexing them offers little value.

Examples:

• the
• is
• and
• a
• of

These are called stop words.

Stoplists improve:

• Index size
• Query performance
• Search quality

Custom stoplists may also be created.

Thesaurus Files

SQL Server supports synonym expansion through thesaurus XML files.

Example:

Searching:

car

may automatically include

automobile
vehicle

This improves keyword searches without requiring embeddings.

Supported Languages

Full-text search supports dozens of languages.

Language-specific processing includes:

• tokenization
• stemming
• stop words
• word breakers

Examples include:

• English
• French
• German
• Spanish
• Japanese
• Chinese

Each language has its own linguistic rules.

Maintaining Full-Text Indexes

Indexes require updates when data changes.

Population modes include:

Full Population

Rebuilds the entire index.

Suitable for:

• initial creation
• major updates

Automatic Change Tracking

Automatically updates the index after data modifications.

Recommended for most OLTP workloads.

Manual Population

Administrators trigger updates manually.

Useful when:

• large batch loads occur
• maintenance windows exist

Performance Considerations

Full-text search is highly optimized but requires planning.

Consider:

• index storage
• population time
• update frequency
• large document sizes
• language configuration
• stoplists

For massive document repositories, automatic population should be monitored to avoid excessive resource usage.

When to Use Full-Text Search

Choose full-text search when users search by:

• keywords
• phrases
• document titles
• product names
• legal terminology
• technical documentation

Examples:

• Knowledge bases
• Product catalogs
• Documentation portals
• Legal document repositories
• Medical reference systems

When NOT to Use Full-Text Search

Full-text search is not ideal when users expect semantic understanding.

Example:

User searches:

“recover my account”

Stored document:

“reset your password”

These phrases contain different words.

Full-text search may not match them effectively.

Semantic vector search would perform much better.

Full-Text Search vs LIKE

Full-Text Search vs Semantic Vector Search

Common DP-800 Scenarios

Scenario 1

A legal team searches contracts using exact legal terminology.

Best solution: Full-text search.

Scenario 2

A documentation portal searches millions of technical articles.

Best solution: Full-text search.

Scenario 3

An AI assistant answers questions using company documentation.

Best solution: Hybrid search (full-text + vector search).

Scenario 4

A recommendation engine finds similar documents.

Best solution: Vector search.

Best Practices

• Use full-text indexes instead of LIKE for large text searches.
• Configure the correct language for linguistic processing.
• Enable automatic change tracking for frequently updated data.
• Use stoplists to reduce index size and improve relevance.
• Use CONTAINS() for precise searches and FREETEXT() for natural-language style queries.
• Use CONTAINSTABLE() or FREETEXTTABLE() when relevance ranking is required.
• Consider hybrid search when applications require both keyword precision and semantic understanding.
• Monitor full-text index population and maintenance in production environments.

DP-800 Exam Tips

• Know the differences between CONTAINS(), FREETEXT(), CONTAINSTABLE(), and FREETEXTTABLE().
• Understand how full-text indexes differ from traditional SQL indexes.
• Remember that full-text search is keyword-based, while vector search is meaning-based.
• Understand the purpose of stoplists, word breakers, stemmers, and thesaurus files.
• Expect scenario-based questions asking you to choose between LIKE queries, full-text search, vector search, and hybrid search based on application requirements.
• Know when full-text search is sufficient and when semantic search or hybrid search provides a better user experience.

Practice Exam Questions

Question 1

A company stores millions of technical articles in an Azure SQL Database. Users frequently search for exact product names and technical terms. Developers currently use the following query:

SELECT *
FROM Articles
WHERE Content LIKE '%Azure SQL%'

The search is becoming increasingly slow as the table grows.

Which feature should you recommend?

A. Full-text search
B. Columnstore indexes
C. Semantic vector search
D. Table partitioning

Correct Answer: A

Explanation

Full-text search is specifically designed for efficient searching of large text columns. It creates specialized indexes that support keyword searches, phrase matching, ranking, and linguistic analysis. While table partitioning and columnstore indexes improve other workloads, they do not replace full-text search functionality.

Question 2

Which SQL Server function searches for exact words, phrases, Boolean expressions, and prefix terms?

A. FREETEXT()
B. CONTAINS()
C. PATINDEX()
D. CHARINDEX()

Correct Answer: B

Explanation

CONTAINS() supports advanced search expressions including:

• Exact words
• Exact phrases
• Boolean operators (AND, OR, AND NOT)
• Prefix searches
• Proximity searches

FREETEXT() is intended for natural-language searching rather than precise keyword expressions.

Question 3

A developer wants search results to include different grammatical forms of the word run, such as:

• running
• runs
• ran

Which SQL Server component provides this capability?

A. Stoplists

B. Full-text catalogs

C. Stemmers

D. Clustered indexes

Correct Answer: C

Explanation

Stemmers recognize different inflectional forms of words based on language-specific rules. This allows a search for “run” to also return documents containing “running,” “runs,” or “ran.”

Question 4

Which statement best describes a full-text catalog?

A. It stores database backups.

B. It replaces clustered indexes.

C. It is a logical container that organizes one or more full-text indexes.

D. It stores vector embeddings.

Correct Answer: C

Explanation

A full-text catalog is a logical container for full-text indexes. While SQL Server automatically manages catalogs in newer versions, understanding their role remains important for administration and exam scenarios.

Question 5

Which function is most appropriate when users enter natural-language search phrases rather than precise keywords?

A. CONTAINS()

B. LIKE

C. FREETEXT()

D. PATINDEX()

Correct Answer: C

Explanation

FREETEXT() performs natural-language searches by considering linguistic analysis, stemming, and synonyms. It is designed for less structured search input compared to CONTAINS().

Question 6

Which full-text search feature helps reduce index size by excluding commonly occurring words such as the, is, and and?

A. Word breakers

B. Stoplists

C. Stemmers

D. Ranking tables

Correct Answer: B

Explanation

Stoplists contain common words, known as stop words, that are ignored during indexing and searching. This improves both index efficiency and search relevance.

Question 7

Your application must display search results ordered from the most relevant document to the least relevant.

Which functions are specifically designed for this purpose?

A. CONTAINS() and FREETEXT()

B. LIKE and PATINDEX()

C. CONTAINSTABLE() and FREETEXTTABLE()

D. CHARINDEX() and STRING_SPLIT()

Correct Answer: C

Explanation

CONTAINSTABLE() and FREETEXTTABLE() return a RANK value that indicates the relevance of each result, allowing applications to sort documents by search quality.

Question 8

Which scenario is the best use case for traditional full-text search?

A. Finding semantically similar customer support tickets

B. Building a Retrieval-Augmented Generation (RAG) chatbot

C. Recommending similar research papers based on meaning

D. Searching legal documents using exact legal terminology

Correct Answer: D

Explanation

Full-text search excels when users search using precise words and phrases, making it well suited for legal, compliance, technical documentation, and product catalog scenarios. Semantic vector search is generally preferred for AI assistants and recommendation systems.

Question 9

Which component is responsible for separating text into searchable words based on language-specific rules?

A. Word breakers

B. Stoplists

C. Embedding models

D. Full-text catalogs

Correct Answer: A

Explanation

Word breakers tokenize text into individual searchable terms according to the linguistic rules of the configured language. Proper tokenization is essential for accurate indexing and querying.

Question 10

A company is building an AI-powered knowledge assistant. Users expect searches such as:

“recover my account”

to return documents titled:

“reset your password”

Which recommendation is most appropriate?

A. Continue using LIKE queries

B. Use only full-text search

C. Replace all searches with clustered indexes

D. Combine full-text search with semantic vector search using hybrid search

Correct Answer: D

Explanation

Full-text search primarily matches keywords and phrases, while semantic vector search retrieves documents based on meaning. Hybrid search combines both approaches, producing more accurate results for AI-powered applications such as RAG systems and enterprise knowledge assistants.

DP-800 Exam Tips

• Use full-text search when exact keywords, phrases, and language-aware matching are required.
• Understand the differences between CONTAINS(), FREETEXT(), CONTAINSTABLE(), and FREETEXTTABLE().
• Remember that word breakers tokenize text, stemmers recognize grammatical variations, and stoplists remove common words to improve search efficiency.
• Use ranking functions when applications need to order search results by relevance.
• Recognize that LIKE queries are not appropriate for large-scale enterprise text search.
• Know that full-text search is keyword-based, while vector search is meaning-based; hybrid search combines the strengths of both and is often the preferred approach for AI-enabled search solutions.

Go to the DP-800 Exam Prep Hub main page