Overview of Full-Text Searching

In SQL Server 2025, full-text searching is a feature used to efficiently query character-based data stored in SQL Server tables. It provides indexing and querying capabilities for natural-language searches over large amounts of textual data, such as documents, product descriptions, or articles, going well beyond what a simple pattern match can offer.

Full-text searching gives you three capabilities that ordinary pattern matching does not:

• Searching for words or phrases in character columns, specifically char, varchar, nchar, and nvarchar.
• Linguistic-based searching, which matches inflectional forms of a word: a search for "run" can also match "running" or "ran" without you having to list every variant yourself.
• Advanced queries using operators such as AND, OR, NEAR, and FORMSOF, along with prefix wildcards for partial-word matching.

Four pieces work together to make full-text searching possible:

• Full-Text Catalog: a container for one or more full-text indexes.
• Full-Text Index: created on one or more columns in a table. Unlike a regular index, it is word-based rather than character-based, which is what lets it match on meaning and word form rather than exact byte sequences.
• Full-Text Filter Daemon: a separate process from the main SQL Server engine. For security and stability reasons, filters and word breakers load inside this separate process rather than inside the database engine itself, and it is what actually parses and indexes the textual data.
• Stoplists and Language Support: stop words, such as "the," "a," and "is," are excluded from the index by default, and SQL Server supports multiple languages so linguistic relevance is calculated correctly for each column.

Imagine a Products table with a Description column. Once a full-text index exists on that column, you can search for all products that mention the word "laptop" near the word "battery," which a plain LIKE search has no way to express at all:

SELECT Name, Description
FROM Products
WHERE CONTAINS(Description, 'NEAR((laptop, battery))');

This single predicate finds rows where both words appear close to each other, in either order, without you having to guess how far apart they might be written.

Getting full-text searching working on a table involves four steps, each one building on the last:

1. Enable Full-Text Search. Install the Full-Text and Semantic Extractions for Search feature during SQL Server setup; it is not installed by default.
2. Create a full-text catalog. A catalog has to already exist before you can create an index on it; SQL Server will not create one for you automatically. Mark a catalog as the default, or name a catalog explicitly in the next step:

   CREATE FULLTEXT CATALOG ProductCatalog AS DEFAULT;
   

3. Create a full-text index. The KEY INDEX clause has to reference an existing index that is unique, single-column, and does not allow nulls:

   CREATE FULLTEXT INDEX ON Products(Description)
   KEY INDEX PK_Products
   ON ProductCatalog;
   

4. Query using full-text functions. CONTAINS() finds rows that match specific criteria, such as exact words, prefixes, proximity terms, or inflectional forms. FREETEXT() finds rows that match the meaning of a phrase using natural-language matching, rather than a precise set of criteria you have to construct yourself.

The following table summarizes the pieces covered so far:

To use full-text searching at all, the SQL Server Full-Text Filter Daemon Launcher service has to be running. This is the modern service behind what, in much older versions of SQL Server, was called the Microsoft Search service; the name changed along with the architecture, but the underlying job, loading filters and word breakers outside the main engine process, has stayed the same.

By default, a full-text index uses automatic change tracking. As the underlying data changes, SQL Server tracks those changes and propagates them to the index on its own, in the background, without any manual step on your part. That update is asynchronous, so a change can take a short time to show up in search results, but no stored procedure call is required just to keep the index current. Manual population is available as an option, set with CHANGE_TRACKING MANUAL and applied on demand with ALTER FULLTEXT INDEX ... START UPDATE POPULATION, for cases where you want tighter control over exactly when the index is rebuilt. That option exists for control, not because it is how full-text indexing behaves by default.

Full-text catalogs are mappings of data that speed the search for specific blocks of text within columns that have full-text searching enabled. Prior to SQL Server 2008, full-text catalogs were stored external to the database, which created some significant backup and recovery issues of its own. As of SQL Server 2008, full-text catalogs have been integrated into the main database engine and its storage mechanisms, removing that separation entirely. Due to their complex internal structure, the deeper mechanics of full-text indexes are beyond the scope of this text.

In the next lesson, you will learn about the components you should consider when using full-text searching.