SQL Server FreeText StopLists - StopWords

Today I tried to run the Following Contains Statements and NO Records were returned..

Select * From CategoriesSKus where Contains(AttList, '"|9|"')

Problem is...word "9" is too common and FreeText was simply ignoring it.

in Sql Server 2008, you can create StopWord List and Assign them to your tables. this way you have more control over your ignore / stop words.

here is how you do it....

create fulltext stoplist AttListDigits;

alter fulltext stoplist AttListDigits
  add '9' language 1033; -- 1033 = English

alter fulltext index on CategoriesItems
  set stoplist AttListDigits;

alter fulltext stoplist AttListDigits
  drop '9' language 1033;


So above we created a stoplist called AttListDights and Added Number 9 to it and attached it to table CategoriesItems.

So see your default STOPWORDS LIST....

select * from sys.fulltext_system_stopwords

Posted by:

Comments History

by: on
See Which Table using which FreeText SQL StopList (2008 + ):

SELECT object_name(object_id) AS IndexedObject,
CASE ISNULL(fi.stoplist_id, -1)
 WHEN 0 THEN 'System Stoplist'
 WHEN -1 THEN 'No Stoplist'
 ELSE fs.name
END AS UsedStoplist
FROM sys.fulltext_indexes fi
LEFT JOIN sys.fulltext_stoplists fs ON fi.stoplist_id = fs.stoplist_id;

Clear a Table from using any Stop List:


Name :  

Email :  

Comment Below