SQL Server full-text search: when a stop word broke our search
A full-text search built on SQL Server stopped returning results for valid queries, and the reason was the word 'both' sitting on the English stoplist, so it never entered the index. The author shows how sys.dm_fts_parser exposes the Noise Word and weighs four fixes before turning the stoplist off entirely.
Key points
- A stop word on the English system stoplist is never added to the full-text index, so it cannot match anything.
- In a boolean CONTAINS query a missing stop word makes SQL Server return zero rows instead of evaluating the remaining terms.
- sys.fulltext_system_stopwords and sys.dm_fts_parser reveal this: the term is reported as a Noise Word.
- The fix here was ALTER FULLTEXT INDEX ... SET STOPLIST = OFF, which indexes every word.
- A custom stoplist created FROM SYSTEM STOPLIST and narrowed with DROP is the cheaper option when only a few words misbehave.
- Track repopulation with FULLTEXTCATALOGPROPERTY(..., 'PopulateStatus') and verify the keyword with sys.dm_fts_index_keywords.
A production search built on SQL Server Full-Text Search suddenly returned no rows for queries that used to work, and the culprit was the single word "both". Because 'both' sits on the English system stoplist, it is never written to the full-text index, and with boolean operators inside CONTAINS SQL Server does not quietly ignore the missing term; it fails the whole condition and returns zero rows.
The team traced this with sys.fulltext_system_stopwords and sys.dm_fts_parser, where the term shows up as a Noise Word, then switched the index to a stoplist of OFF, which means every word is indexed and searchable. The catch is cost: bigger indexes, longer population runs and matches for near-universal words. If only a few terms cause trouble, a custom stoplist derived from the system one is the narrower fix.
Real-world use
- A product catalog where users combine several words in one search box and expect all of them to be honoured.
- A document archive on SQL Server where a stop word in a multi-term query silently returns an empty result page.
- A multi-language application that needs one indexing decision per language instead of a hardcoded word list in code.
Why it matters
If your search relies on CONTAINS and users type several words at once, a single common word can return an empty page and look like a data bug; knowing the parser and the stoplist ends the guessing.