Sql-server – Removing some English token words (such as “are” or “or” etc.) from the system stoplist of full text search

full-text-searchindexsql serversql-server-2008sql-server-2008-r2

One of our table has full text catalog on it. It contains some data in a language other than English which makes some words such as "are", "or" useful. There is no stop list defined but these words are considered as system stop lists therefore won't appear in the index keywords. How can I remove these words from the system stop list and add them to the indexed keywords?

Thank you.

Best Answer

You can turn off the system stoplist and create your own, eg

ALTER FULLTEXT INDEX ON dbo.yourTable SET STOPLIST = OFF

CREATE FULLTEXT STOPLIST myCustomStoplist;

ALTER FULLTEXT STOPLIST myCustomStoplist ADD 'someWord' LANGUAGE 'English';

ALTER FULLTEXT INDEX ON dbo.yourTable SET STOPLIST = myCustomStoplist

If you create an empty stoplist, it means all words are considered, or to put it another way, words that were previously considered noise words are no longer. You will need to add some words to avoid index bloat, and as mentioned in my other post, if you really leave the word 'a' out of your stoplist, searches for 'a' will return most of your documents, somewhat defeating the object of full-text index ( ie tightly scoped searches ).

Related Question