Kaz on Commerce!

In SQL Server 2008 noise files have changed. They are no longer represented within a physical file (noise___.txt), they are now managed in databases by using objects called stoplists.

Here are some points on stoplist:

• A system stoplist (default stoplist) exists that is used by default by all fulltext indexes.
• The system stoplist is readonly and can not be changed.
• For databases with compatibility levels set to 80 and 90, the system stoplist is always assigned.
• To manage your own stoplist (noise file) you will need to ensure your compatibility level is set to 100 (SQL Server 2008).

Here are some steps to migrate your existing noise files to stoplists:

1. Change the compatibility level of the database your working with to 100 ‘SQL Server 2008’.
2. Create your stoplist (noise file) against your desired database.
   [SQL]
   CREATE FULLTEXT STOPLIST stoplist_name
   FROM SYSTEM STOPLIST
   [/SQL]This will create a new stoplist based on the system stoplist.If you have not done step 1 you may get this error:  ‘Incorrect syntax near ‘STOPLIST’.’
3. Add or drop stopwords:
   [SQL]
   ALTER FULLTEXT STOPLIST stoplist_name DROP ‘can’ LANGUAGE ‘English’;
   [/SQL]
4. Test your change by checking a search queries tokenization result.Find your stoplist ID.[SQL]
   SELECT stoplist_id, name FROM sys.fulltext_stoplists
   [/SQL]
   Find your language ID.
   [SQL]
   select * from syslanguages
   [/SQL]
   Get the tokenization results.
   [SQL]
   SELECT special_term, display_term
   FROM sys.dm_fts_parser
   (‘ “can testing for fruit and nuts, any type of nut” ‘, 1033, 6, 0)
   [/SQL]
5. Set your stoplist to be used by your full text indexs.[SQL]
   ALTER FULLTEXT INDEX ON dbo.[Database_Name] SET STOPLIST stoplist_name;
   [/SQL]
6. Rebuild your full text indexes.[SQL]
   ALTER FULLTEXT CATALOG [FullTextCatalog_Name] REBUILD
   [/SQL]

This query to check the details of your fulltext_index. It will list the linkage from your fulltext_index to your fulltext_catalog at stoplist.
[SQL]
SELECT t.name AS [TableName], c.name AS [FTCatalogName], c.fulltext_catalog_id,
i.change_tracking_state_desc, i.stoplist_id, s.name AS [StoplistName]
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
LEFT OUTER JOIN sys.fulltext_stoplists AS s
ON i.stoplist_id = s.stoplist_id
[/SQL]

References:
http://msdn.microsoft.com/en-us/library/cc280405.aspx
http://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/