Full-Text Search in SQL Server 2005

Full-Text search is a SQL Server feature that provides powerful options for querying text information in the database.Without this feature the querying options are pretty much limited to using the LIKE clause or using functions such as PATINDEX and CHARINDEX.

The Full-Text Search is implemented via a Full-Text Search Windows Service. This Service is NOT a part of  the SQL Server. The SQL Server interacts with this external service in order to facilitate Full-Text Search.

Following are the steps to enable Full-Text Search in a database:

• Make a list of tables that need to be Full-Text search enabled.
• In each of the tables, make a list of all the columns that need to be a part of the Full-Text Search index.
• Create a Catalog, specifying a location for its creation. A Catalog is a container that holds Indexes. You can have as many Catalogs as you like and each Catalog can hold zero or more indexes. Though external to the SQL Server, the Catalogs need to be on same machine as the SQL Server. For performance reasons, Microsoft recommends creating the Catalogs in a drive separate from that of  the SQL Server installation. Microsoft also recommends creating separate Catalogs for tables that have more than one million rows of data.
• Create one Index per table. You can have only one index per table. Also you need to specify the Catalog the index belongs to.An Index can belong to only one Catalog.
• At this point, we only have the structure(catalogs and indexes) but no data. We need to populate(crawl) the indexes. Population is the process of SQL Server passing data from the indexed columns to the Full-Text Service, which maintains a word list letting us know what words can be found in what rows. It is a resource and memory intensive action that must be performed during off-peak hours so as to not slow down the SQL Server.Once the population is complete, the tables are Full-Text Search enabled.
• Next, all the database objects like Stored Procedures, Functions and Views need to be modified to use the Full-Text syntax. The Full-Text syntax contains commands like CONTAINS,CONTAINSTABLE, FREETEXT, FREETEXTTABLE etc that enable us to query for information in different ways.
• Full-Text indexes are not kept up to date like SQL Server indexes.So, if new data is added to the tables, the data will not be searchable until the indexes on those tables are re-populated.