Full-Text Search in SQL Server

LIKE '%invoice%' scans every row and can't use a regular index when the pattern starts with a wildcard. Full-text search solves this with a separate word-based index: it tokenizes text into words, applies language rules, and answers word and phrase queries orders of magnitude faster than LIKE on large tables.

SQL Server has shipped full-text search since the 90s. It is a distinct engine component -- you may have to install it -- with its own index type, its own query predicates (CONTAINS, FREETEXT), and a few sharp edges that surprise people coming from PostgreSQL's tsvector or MySQL's FULLTEXT.

Check That It's Installed

Full-text search is an optional feature ("Full-Text and Semantic Extractions for Search" in setup). Check before anything else:

SELECT SERVERPROPERTY('IsFullTextInstalled');  -- 1 = installed

If it returns 0, re-run SQL Server setup and add the feature. Azure SQL Database and Managed Instance include it by default.

Setup: Catalog, Key Index, Full-Text Index

Full-text indexes need three pieces: a catalog (a logical container), a unique, single-column, non-nullable key index on the table, and the full-text index itself.

CREATE TABLE articles (
    id     int IDENTITY PRIMARY KEY,
    title  nvarchar(200)  NOT NULL,
    body   nvarchar(max)  NOT NULL
);
 
CREATE FULLTEXT CATALOG article_catalog;
 
CREATE FULLTEXT INDEX ON articles (title, body LANGUAGE 1033)
    KEY INDEX PK__articles__id  -- use your actual PK index name
    ON article_catalog
    WITH CHANGE_TRACKING AUTO;

Notes:

• One full-text index per table. Put every searchable column in it.
• The KEY INDEX must reference the name of a unique index, not the column. Find it with EXEC sp_helpindex 'articles', or name your PK explicitly (CONSTRAINT pk_articles PRIMARY KEY) so you're not stuck with the auto-generated PK__articles__3213E83F... name.
• LANGUAGE 1033 (English) controls the word breaker and stemmer per column. Match it to the content's language, not the server's.

CONTAINS: Precise Word and Phrase Queries

CONTAINS is the precise predicate -- you say exactly what to match:

-- single word
SELECT id, title FROM articles WHERE CONTAINS(body, 'replication');
 
-- phrase (double quotes inside the string)
SELECT id, title FROM articles WHERE CONTAINS(body, '"log shipping"');
 
-- boolean operators
SELECT id, title FROM articles WHERE CONTAINS(body, 'backup AND NOT differential');
 
-- any indexed column
SELECT id, title FROM articles WHERE CONTAINS(*, 'failover');

Inflectional and Thesaurus Forms

-- matches run, ran, running
SELECT id FROM articles WHERE CONTAINS(body, 'FORMSOF(INFLECTIONAL, run)');
 
-- synonyms from the (XML-configured) thesaurus file
SELECT id FROM articles WHERE CONTAINS(body, 'FORMSOF(THESAURUS, car)');

The thesaurus is empty by default -- FORMSOF(THESAURUS, ...) does nothing until you edit the per-language tsXXX.xml files and run sp_fulltext_load_thesaurus_file.

Proximity with NEAR

-- words within 5 terms of each other, in any order
SELECT id FROM articles WHERE CONTAINS(body, 'NEAR((index, fragmentation), 5)');

FREETEXT: Loose, Meaning-Oriented Matching

FREETEXT splits the input into words, stems each one, and matches any of them. Good for "search box" queries where the user typed a sentence:

SELECT id, title
FROM articles
WHERE FREETEXT(body, 'how to restore a database backup');

You get broader recall and less control -- there are no operators, phrases, or wildcards inside FREETEXT. Rule of thumb: CONTAINS for structured queries you build, FREETEXT for raw end-user input.

Ranking with CONTAINSTABLE and FREETEXTTABLE

The predicates only filter. To order by relevance, use the table-valued versions, which return the key column ([KEY]) and a RANK:

SELECT a.id, a.title, ft.RANK
FROM CONTAINSTABLE(articles, body, 'backup OR restore') AS ft
JOIN articles AS a ON a.id = ft.[KEY]
ORDER BY ft.RANK DESC;

Two things to know about RANK:

• It is an integer from 0 to 1000, but it is not normalized -- values are only comparable within a single query, not across queries or tables. Don't display it as a percentage.
• The optional fourth argument (top_n_by_rank) limits results before the join, which is the cheap way to paginate: CONTAINSTABLE(articles, body, 'backup', 50).

The Wildcard Rule: Prefix Only

Full-text search supports trailing wildcards only, and the term must be quoted:

-- matches replicate, replication, replica...
SELECT id FROM articles WHERE CONTAINS(body, '"replic*"');

'*flow' (leading wildcard) is not supported -- the index is organized by word prefix. If you genuinely need suffix matching, the standard workaround is storing a reversed copy of the text in another column and prefix-searching that. Without the double quotes, CONTAINS(body, 'replic*') searches for the literal token replic* and silently matches nothing useful.

The Stoplist Trap

Common words ("the", "and", "is") are stopwords and are dropped at indexing time. The gotcha: a CONTAINS phrase or boolean query that includes a stopword can return zero rows even though the text obviously exists, because the stopword can never match.

Fixes, in order of preference:

-- see what the parser does with your input (requires sysadmin)
SELECT * FROM sys.dm_fts_parser('"the quick brown fox"', 1033, 0, 0);
 
-- per-database: treat stopwords as placeholders instead of hard failures
ALTER DATABASE CURRENT SET TRANSFORM_NOISE_WORDS = ON;
 
-- or create a custom stoplist without the offending word
CREATE FULLTEXT STOPLIST my_stoplist FROM SYSTEM STOPLIST;
ALTER FULLTEXT STOPLIST my_stoplist DROP 'the' LANGUAGE 1033;
ALTER FULLTEXT INDEX ON articles SET STOPLIST = my_stoplist;

sys.dm_fts_parser is the single most useful debugging tool here -- it shows exactly which tokens the word breaker produced and which were treated as noise.

Population Is Asynchronous

With CHANGE_TRACKING AUTO, SQL Server updates the full-text index in the background after DML commits. A row you just inserted is not guaranteed to be searchable immediately -- typically it appears within seconds, but there is no transactional consistency between the table and the full-text index. This is the same eventual-consistency model as PostgreSQL's GIN fastupdate pending list, only more visible.

Check population status:

SELECT FULLTEXTCATALOGPROPERTY('article_catalog', 'PopulateStatus');  -- 0 = idle
OBJECTPROPERTYEX(OBJECT_ID('articles'), 'TableFulltextPendingChanges');

If your workflow inserts a row and searches for it in the same request, full-text search is the wrong tool -- use LIKE for that path or rethink the flow.

What About Semantic and Vector Search?

Two different features share the "semantic" name:

• Statistical Semantic Search (2012-era, SEMANTICKEYPHRASETABLE etc.) is legacy: it extracts key phrases and document similarity from full-text indexes. It still exists, but it hasn't seen meaningful investment in years and is not what current docs mean by semantic search.
• SQL Server 2025 adds a native vector type and vector search (embeddings + approximate nearest neighbor). That is a separate feature from full-text search -- it matches by meaning via an embedding model, not by words. For keyword search, full-text remains the right tool; for "find similar meaning," 2025's vector search is the modern answer.

Full-Text vs LIKE: When to Use Which

Common Mistakes

• Unquoted wildcard: CONTAINS(col, 'term*') without inner double quotes doesn't prefix-match.
• Phrase with a stopword returns nothing -- check sys.dm_fts_parser, set TRANSFORM_NOISE_WORDS ON.
• Treating RANK as a percentage -- it's only ordinal within one query.
• Expecting instant visibility of new rows -- population is asynchronous.
• Wrong LANGUAGE on the indexed column -- stemming and word breaking silently misbehave for non-English content.

Related Guides

• Indexes in SQL Server explained
• Querying JSON in SQL Server
• Full-text search in PostgreSQL
• Full-text search in MySQL

Mako connects to SQL Server with AI-powered autocomplete. Try it free at mako.ai.