What is a full-text index in SQL Server?
Quick Answer
A full-text index tokenises text columns (word-breaking, stemming, stop words) so you can search for words and phrases with CONTAINS/FREETEXT far faster than a leading-wildcard LIKE '%word%', which can't use a normal index at all and forces a full scan.
A full-text index breaks text into words, handles things like stemming and stop words, and lets you query with CONTAINS or FREETEXT instead of a LIKE '%keyword%'. That distinction matters because a leading-wildcard LIKE can't use a regular index at all — it's a full table scan — while a full-text index makes that kind of search fast and lets you do boolean and proximity queries too. It's populated on a schedule rather than updated instantly on every write, so there's a small lag. For a genuinely serious search experience — relevance ranking, facets, fuzzy matching at scale — I'd reach for a dedicated search engine instead, but for search-a-support-ticket-body inside the app, full-text is often enough.
CONTAINS(Body, 'SQL AND performance')supports boolean, proximity and prefix search;FREETEXTdoes looser natural-language matching.- Built and maintained asynchronously (population schedule), not updated instantly like a B-tree index.
- Good for moderate search needs baked into the database; for real search UX (ranking, facets, typo tolerance, huge scale) a dedicated engine (Elasticsearch, Azure AI Search) is the better tool.