My customer wanted a search screen to find product information by key words. Up to now I have been using standard SQL server transact-sql. It became clear to me that we needed to look into the full text search because he wanted to find data by relevance.
The database needs to be readied to use full text search.
Set the database to compatibility level server 2008.
Here is the transact SQL:
-- enable full text search on a sql 2008 server - database was restored from sql 2000
-- examine the current compatibility level
EXEC sp_dbcmptlevel 'MyDatabase'
-- change the compatibility level
ALTER DATABASE [MyDatabase] SET COMPATIBILITY_LEVEL = 100
Create a stoplist
In case the system stoplist does not contain a stopword that you require
-- find out if a specific stopword is in use
select * from sys.fulltext_stopwords where stopword = 'de'
-- in case the system stoplist is not sufficient, create your own
CREATE FULLTEXT STOPLIST myStoplist FROM SYSTEM STOPLIST;