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.
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
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
SELECT stopword, lcid, name FROM sys.fulltext_system_stopwords ssw JOIN sys.fulltext_languages slg ON slg.lcid = ssw.language_id WHERE stopword = '7'
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.
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)