Stoplists are used in SQL Server Full Text Indexes to exclude common “noise” words (such as to, the, etc.) from filling up the index with information that is not very useful for Full Text Searches. By default, a new Full Text Index in SQL Server 2008 will use the system stoplist, which has about 15K different words in it.
You can create your own stoplist, either from scratch or by copying the system stoplist into it. Then you can modify your stoplist as you see fit, by adding or removing words. You can then change a Full Text Index to use your custom stoplist. You will have to repopulate the full text index for the new stoplist to take full effect.
At any rate, here are some useful queries if you need to manipulate your stoplist(s).
-- Some Useful FT Related Stoplist Queries -- Glenn Berry 7/21/2009 -- Get base table names, FTCatalogNames and change tracking type SELECT t.name AS [TableName], c.name AS [FTCatalogName], c.fulltext_catalog_id, i.change_tracking_state_desc FROM sys.tables AS t INNER JOIN sys.fulltext_indexes AS i ON t.object_id = i.object_id INNER JOIN sys.fulltext_catalogs AS c ON i.fulltext_catalog_id = c.fulltext_catalog_id; -- Get all the non-system stoplists SELECT stoplist_id, name FROM sys.fulltext_stoplists; -- Create a stoplist from the system stoplist CREATE FULLTEXT STOPLIST NewsGatorSL FROM SYSTEM STOPLIST; -- Look for a term in the stoplist SELECT stoplist_id, stopword, [language], language_id FROM sys.fulltext_stopwords WHERE stopword = 're' -- Drop a stoplist DROP FULLTEXT STOPLIST TestSL; -- Drop a term from a stoplist ALTER FULLTEXT STOPLIST NewsGatorSL DROP 're' LANGUAGE 1033; -- Change the stoplist for a fulltext index ALTER FULLTEXT INDEX ON CurrentPostFullTextMonday -- This is base table name SET STOPLIST NewsGatorSL -- Repopulate FT Index after changing stoplist ALTER FULLTEXT INDEX ON dbo.CurrentPostFullTextMonday START UPDATE POPULATION GO -- Get document counts for a display term SELECT display_term, column_id, document_count FROM sys.dm_fts_index_keywords(DB_ID('ngfulltext1'), OBJECT_ID('CurrentPostFullTextMonday')) WHERE display_term = 'Re'