Full Text Search in SQL Server 2005 Part 2

I will be showing how to configure and maintain Full Text Search catalog in Microsoft SQL Server 2005 in this article. However, if you are new to FTS and want to get basic idea about the same then you can refer my previous article at http://ritesh-a-shah.blogspot.com/2009/03/full-text-search-in-sql-server-2005.html

Configuring Full Text Catalog (FTC):

You can use one FTC for one database, one database may have more than one FTC but you can’t use same FTC in other database. It is not sharable. FTC is nothing but just a collection of Full-Text indexes. One FTC may store more than one index for more than one table but each table can belongs to one FTC only. You can’t not create FTC on system table, table variable, views and temporary table.

You can configure Full Text Catalog by wizard of SSMS and from T-SQL Script. Since I am a script bee, I will explain T-SQL method.

--Enable database for FTS

use adventureworks

exec sp_fulltext_database 'enable'

GO

--creating catalog with the name 'FirstFTC-AdventureWorks

exec sp_fulltext_catalog 'FirstFTC-AdventureWorks', 'Create'

GO

--mark table for full text search with primary key

exec sp_fulltext_table 'HumanResources.Employee','Create','FirstFTC-AdventureWorks','PK_Employee_EmployeeID'

GO

--add column to the catalog, NOTE: you can give more then one column also

exec sp_fulltext_column 'HumanResources.Employee','LoginID','Add'

GO

--activate table for FTS

exec sp_fulltext_table 'HumanResources.Employee','activate'

GO

--run FTS index on table

exec sp_fulltext_table 'HumanResources.Employee','start_full'

GO

Wow, you have just successfully created one catalog for Full Text Search now what if you wish to maintain it.

You can maintain Full Text Search in two ways. “Incremental” and “Change Tracking and background population”

If you have Times stamp column in your table and you want to update your index at specific time, may be in the evening every day, you can use incremental.

If you wish SQL-Server to track back and keep watch on FTS enabled table and update the index automatically as and when data gets changed than you should go for “Change Tracking and Background population”.

-- start incremental index update at specific time

exec sp_fulltext_table 'HumanResources.Employee','start_incremental'

GO

-- start tracking and background update index automatically

exec sp_fulltext_table 'HumanResources.Employee','start_change_tracking'

exec sp_fulltext_table 'HumanResources.Employee','start_background_updateindex'

GO

--deleting catalog with the name 'FirstFTC-AdventureWorks

exec sp_fulltext_catalog 'FirstFTC-AdventureWorks', 'Drop'

GO

--rebuilding index 'FirstFTC-AdventureWorks

exec sp_fulltext_catalog 'FirstFTC-AdventureWorks', 'rebuild'

GO

Herewith, I am finishing this article and now finally my next article shows you how to select data with Full Text Catalog in Microsoft SQL-Server 2005

Reference: Ritesh Shah