SQL 2008: Turn off Stop Words for Full Text Search Query

Question

I'm having quite a bit of difficulty finding a good solution for this:

Let's say I have a table of "Company", with a column called "Name". I have a full-text catalog on this column. If a user searched for "Very Good Company", my query would be:

SELECT     * FROM     Company WHERE     CONTAINS(Name, '"Very" AND "Good" AND "Company"') 

The problem is in this example, the word "Very" shows up in the standard list of stopwords:

SELECT     ssw.* FROM     sys.fulltext_system_stopwords ssw WHERE     ssw.language_id = 1033; 

Resulting in the query returning with no rows, even though there is a row with the name "Very Good Company".

My question is, how would I go about turning the stopwords off for my query? Or how would I go about removing them entirely?

Or is there another way I should be going about this search?

Answer 1

In case anyone else stumbles upon this problem:

It looks like there is an option to do this in 2008; it wasn't apparent to me because the database was upgraded from 2005 where I don't believe this was an option.

The first thing you need to do is set the compatibility level up to 2008:

ALTER DATABASE [MyDatabase] SET COMPATIBILITY_LEVEL = 100 

Then, when creating the full-text index through the wizard, there is a step that allows you to ignore stopwords for the index

edit: Here's the script to do it as well:

ALTER FULLTEXT INDEX ON MyTable SET STOPLIST = OFF 

Answer 2

By default in SQL Server the stopwords are not ignored.

This is what you want to do:

sp_configure 'show advanced options', 1; RECONFIGURE; GO sp_configure 'transform noise words', 1; RECONFIGURE; GO 

REF: http://msdn.microsoft.com/en-us/library/ms187914%28v=sql.100%29.aspx