Creating and Maintaining Full-text Indexes

Full-text catalogs and indexes can be defined and managed using:

• The SQL Server Enterprise Manager GUI and wizards.
• Applications that call the SQL Distributed Management Objects (SQL-DMO) full-text catalog and full-text index objects and methods.
• Applications that use Transact-SQL and call system stored procedures for managing full-text catalogs and indexes.

Using Transact-SQL as an example, the steps to define, activate, and populate full-text indexes are:

1. Install the Microsoft Search service during SQL Server Setup, and then run the service. You must choose the Custom setup option and select the Full-text search subcomponent.
2. Enable the database to support full-text indexes by calling sp_fulltext_database.
3. Create each full-text catalog is created by calling sp_fulltext_catalog with the create option.
4. Associate each table that will have a full-text index with a catalog and its index named by calling sp_fulltext_table with the create option.
5. Add each column that participates in a full-text index to the index definition by calling sp_fulltext_column with the add option.
6. Establish the full-text start-seed value for each table by calling sp_fulltext_table with the activate option.
7. Populate all of the full-text indexes in a catalog at one time by calling sp_fulltext_catalog with the start_full option.

Full-text indexes are not kept up to date automatically as data is modified in the associated tables. Tables that can be updated should have their full-text indexes repopulated at appropriate intervals. The population can be time-consuming, so it is an asynchronous process that is usually run in the background. The population process has to perform sometimes complex linguistic analysis on the source strings; scanning the strings to determine word boundaries (called word-breaking) and eliminating noise-words. Both full and incremental population is supported, and is started by calling sp_fulltext_catalog. It is best to schedule jobs that run periodically to do this.

The data in full-text catalogs and indexes are not recovered by a system recovery. They are also not backed up or restored by the BACKUP and RESTORE statements. After a recovery or restore operation, the full-text catalogs and indexes should be resynchronized with the base tables. Because the metadata defining the full-text catalogs and indexes is stored in the database, they can be repopulated easily after the database has been recovered.

See Also

Full-text Indexes