SQL Server Stopwords and Stoplists for Full-Text Search

By default FTS index uses system stoplist. If you want to have custom one, you need to create new first. To create a full-text stoplist in Management Studio refer to Configure and Manage Stopwords and Stoplists for Full-Text Search
In my case I used either “Create from the system stoplist” (and did modifications) or “Create an empty stoplist”.

REMEMBER after creating new stoplist:
– you have to make sure your full-text index uses this stop list and rebuild your catalog.
– Right click on your table, select full-text index then properties, and in the general tab click on the full-text stop list drop down and pick your stop list.
http://social.msdn.microsoft.com/Forums/en-US/sqlsearch/thread/91774a49-7362-41ad-ac91-7bd453cc94c6/

SQL Server 2008 provides three catalog views that are useful for retrieving the contents of system and custom stoplists:

SELECT * FROM sys.fulltext_system_stopwords;
SELECT * FROM sys.fulltext_stoplists;
SELECT * FROM sys.fulltext_stopwords;

For example get all available language names

SELECT DISTINCT slg.name
FROM sys.fulltext_system_stopwords ssw
JOIN sys.fulltext_languages slg
ON slg.lcid = ssw.language_id;

To do modifications on custom stoplist you can either use Management Studio (right click on the list then Properties) or SQL queries.

Digits or single characters problem

For example you want to search in your table ‘7 seats’ in your cars description table. It will find all having just ‘seats’ keyword because by default single digits and characters are stopwords.
To make sure you can use variations of queries below

SELECT stopword, lcid, name
FROM sys.fulltext_system_stopwords ssw
JOIN sys.fulltext_languages slg
ON slg.lcid = ssw.language_id

stoplist_01

SELECT stopword, lcid, name
FROM sys.fulltext_system_stopwords ssw
JOIN sys.fulltext_languages slg
ON slg.lcid = ssw.language_id
WHERE slg.lcid = 1033

stoplist_02

SELECT stopword, lcid, name
FROM sys.fulltext_system_stopwords ssw
JOIN sys.fulltext_languages slg
ON slg.lcid = ssw.language_id
WHERE stopword = '7'

stoplist_03

Stoplist modifications

To fix ‘7 seats’ all I had to do is remove ‘7’ single digit from the list using query

ALTER FULLTEXT STOPLIST MyList DROP '7' LANGUAGE 'English';

(Notice ‘;’ at the end)

When I wanted to do bulk update I used query below to generate bunch of other ones.

SELECT 'ALTER FULLTEXT STOPLIST MyList DROP ''' + stopword + ''' LANGUAGE ''' + name COLLATE DATABASE_DEFAULT + ''';'
FROM sys.fulltext_system_stopwords ssw
JOIN sys.fulltext_languages slg
ON slg.lcid = ssw.language_id
WHERE stopword = '7'
  • pay attention to COLLATE DATABASE_DEFAULT added to query – name field is conflicting in this query
  • remember about ‘;’ at the end
  • if you did ok, all of it will run with no problem

Sometimes you get an error like this

The stoplist 'StopList' does not contain fulltext stopword '7' with locale ID 1093. Specify a valid stopword and locale identifier (LCID) in the Transact-SQL statement.

When you have ALTER statements in separate lines, double click on error and SSMS will show you exact line. Then remove it.
Remember, if ALTER was successful in a first run, it will error out if ALTER again.

Remember

Once you are done, you will need to repopulate the affected indexes so that they will include the formerly excluded words in the index(right click on your table, full text index, ‘start full population’)
Sometimes it does not work. You query the table and results as they were before.
What helped me is setting action Repopulate index in Full Text properties or reassigning Full Text Index to System, Actions to Repopulate and assigning back (again, Actions Repopulate Index was set)

stoplist_04

stoplist_05

01

Leave a Reply

Your email address will not be published. Required fields are marked *