Wednesday, December 22, 2010

FULLTEXT STOPLIST SQL 2008

In SQL FULLTEXT, we can discards commonly occurring string from build into FULLTEXT INDEX.
Example : is, an, i, we. Thease discarded string s are called stopwords

In SQL 2005 and below, stoplist is called noise word and define in noise file under fulltext database.
Noise word is applied through SQL Instance

In SQL 2008, we can define STOPLIST in database instance

There is 3 option to select when we create STOPLIST
Create an empty stoplist
Create from the system stoplist
Create from an exisiting full-text stop list

System stoplist
select * from sys.fulltext_system_stopwords

Current database stoplist
select * from sys.fulltext_stoplists

I try create stoplist in adventurework
but sql return with error

CREATE FULLTEXT STOPLIST myStoplist;

"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'STOPLIST'."

I just notice in "Stopwords and Stoplists", it did mention :
"Important CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are supported only under compatibility level 100. Under compatibility levels 80 and 90, these statements are not supported. However, under all compatibility levels the system stoplist is automatically associated with new full-text indexes."

I change the database compatibility level from 90 to level 100 and execute the query again
CREATE FULLTEXT STOPLIST myStoplist;

Stoplist create successful

Reference :
Stopwords and Stoplists
http://msdn.microsoft.com/en-us/library/ms142551.aspx

No comments: