Microsoft SQL Server 2008: Fulltext and Stoplist Association, Manage Multiple Stoplists

Posted: July 27, 2011 in SQL, SQL Server 2008
Tags: , , , ,

Today after having so many days effort, I finally able to find out how we can manage multiple stoplists for a database?

“Why MS-SQL Server 2008 is providing facility to create multiple custom stoplists?”, I was thinking over this and wondering how we can manage any specific stoplist out of multiple stoplist created? I have tried to find out this over internet but finally found in MSDN only. May be because I was searching for my content with inappropreate keywords. What so ever, Finally I found what I wanted.

I wanted to create multiple stop-lists and then wanted to use each one of them in my different requirements for the same database.

For example, There is a Job portal where applicants are searching for their appropreate Jobs, whereas Employers/Companies are searching for the right applicants with some keywords. Now consider that there are two different stop-lists that I would like to manage, one Stop-List will be specific to Job Search only and will be used by applicants for searching appropreate jobs and another stop-list will be specific to applicant’s CV/Resume Search and will be used by Companies to find out right candidate.

How can I manage this?

Well its really simple. You need to follow below steps and some scripts.

1. Create Fulltext Index for a database table that you wanted to include in a search query (e.g. job details or CV details tables)
How to?: http://msdn.microsoft.com/en-us/library/ms187317.aspx

2. Create StopLists of your choice (Custom Stoplist)
How to?: http://msdn.microsoft.com/en-us/library/cc280405.aspx

3. Associate any Stoplist to any fulltext indexed table of your choice
How to?: http://msdn.microsoft.com/en-us/library/ms188359.aspx

(E.g. for 3rd Step you can use a simple SQL script as below one to associate any stoplist to any fulltext indexed table:
ALTER FULLTEXT INDEX ON table_name
SET STOPLIST stoplist_name WITH NO POPULATION ;)

Now if you apply any fulltext search on the tables say for example “Job Details” then fulltext search will ignore the words you have defined into associated stoplist that you have created.

To find out the association between the stoplist and associated fulltext catalog you can execute following SQL query:
select fulltext_catalog_id,stoplist_id, * from sys.fulltext_indexes;

To find out catalog details, you can execute following SQL query:
select * from sys.fulltext_catalogs

Happy Programming.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s