Posts Tagged ‘multiple stoplist and fulltext’

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.