Maintaining Full-Text Indexes

There are three ways to maintain a full-text index:

• Full rebuild

  Rescans all rows. Completely rebuilds the full-text index. You can perform a full rebuild immediately or on a schedule, using SQL Server Agent.

• Timestamp-based incremental rebuild

  Rescans those rows that have changed since the last full or incremental rebuild. This requires a timestamp column on the table. Changes that do not update the timestamp, such as WRITETEXT and UPDATETEXT, are not detected. You can perform an incremental rebuild immediately or on a schedule.

• Change tracking

  Maintains a list of all changes to the indexed data. Changes made with WRITETEXT and UPDATETEXT are not detected. You can update the full-text index with these changes immediately, on a schedule, or as they occur, using the background update index option.

The method you use depends on factors such as the CPU and available memory, the amount and rate of change of data, the amount of available disk space, and the importance of the full-text index being current. Use these recommendations as a guide for selecting a maintenance method.

• Use change tracking with the background update index option when CPU and memory are available, the value of an up-to-date index is high, and immediate propagation can keep up with the rate of changes.
  
  
• Use change tracking with scheduled propagation when CPU and memory can be used at scheduled times, disk space for storing changes is available, and changes between the scheduled times are not so significant that the propagation takes longer than a full rebuild.
  
  
• Use a full rebuild when a large percentage of records change or are added at once. If a large percentage of records change over an extended period of time, consider using change tracking with scheduled or background update index.
  
  
• Use an incremental rebuild when a large number, but not a large percentage, of documents change at one time. If a large number of records change over an extended period of time, consider using change tracking with scheduled or background update index.

See Also

OBJECTPROPERTY

sp_fulltext_table