SQL Server Full-Text Search: building blocks and main features
Bart Wullems walks through SQL Server's built-in Full-Text Search, from the inverted index behind it to the query predicates you write against it. The piece explains why leading-wildcard LIKE queries can't seek an index, then shows how to create a full-text catalog and index, filter with CONTAINS and FREETEXT, rank results with CONTAINSTABLE, and control stop words.
Key points
- A leading-wildcard LIKE can't seek an index; full-text search uses an inverted index instead.
- The full-text index needs a unique, single-column, non-nullable index on the table, usually the primary key.
- CHANGE_TRACKING = AUTO updates the index in the background, so brand-new rows may lag behind.
- CONTAINS supports prefix ("mount*"), phrase, AND/OR/AND NOT, NEAR((a, b), 5) and FORMSOF(INFLECTIONAL, ...).
- CONTAINSTABLE and FREETEXTTABLE expose RANK, which you tune with ISABOUT(... WEIGHT(.8)) for term importance.
- The default system stoplist drops common words; inspect it with sys.fulltext_system_stopwords and swap in a custom list if needed.
On large tables, WHERE Name LIKE '%term%' can't seek an index, so every row is scanned; it also ignores word forms and has no concept of relevance. Full-Text Search instead builds an inverted index: a language-aware word breaker splits text into tokens and a stemmer groups forms like ride, rides and riding. On-premises it is an optional setup feature, checkable with SERVERPROPERTY('IsFullTextInstalled'); Azure SQL Database needs no installation.
Create a full-text catalog, then one full-text index per table over text or binary columns, backed by a unique single-column non-nullable index, usually the primary key. Columns take an LCID such as LANGUAGE 1033, and CHANGE_TRACKING = AUTO refreshes the index in the background. CONTAINS covers prefix and phrase matching, boolean operators, NEAR and FORMSOF(INFLECTIONAL); FREETEXT is looser with less control. CONTAINSTABLE and FREETEXTTABLE return RANK, tunable with ISABOUT and WEIGHT. The default system stoplist skips common words entirely, so some searches quietly return nothing.
Real-world use
- A product catalog search box that should also match inflected forms in the description column
- Ordering support tickets by relevance instead of plain keyword filtering
- Searching inside Word or PDF documents stored in varbinary(max) columns
- An internal app that wants to stay on a single database before adding a separate search cluster
Why it matters
You get language-aware, relevance-ranked search without adding another component to your architecture — but leaving the default stoplist and the asynchronous index updates unexamined can silently drop rows from results.