Full-Text Indexes

A full-text index is a special type of token-based functional index that is built and maintained by the Microsoft Microsoft Full-Text Engine for SQL Server (MSFTESQL) service. The process of building a full-text index is quite different from building other types of indexes. Instead of constructing a B-tree structure based on a value stored in a particular row, MSFTESQL builds an inverted, stacked, compressed index structure based on individual tokens from the text being indexed.

In SQL Server 2005, the size of a full-text index is limited only by the available memory resources of the computer on which the instance of SQL Server is running.

The process of creating and maintaining a full-text index is called index population. Microsoft supports the following types of full-text index population:

• Full population
• Change tracking-based population
• Incremental timestamp-based population

Full Population

Typically occurs when a full-text catalog or full-text index is first populated. The indexes can then be maintained using change tracking or incremental populations.

During a full population of a full-text catalog, index entries are built for all the rows in all the tables covered by the catalog. If a full population is requested for a table, index entries are built for all the rows in that table.

If you do not want the full-text index to be populated at the time it is created, using CREATE FULLTEXT INDEX statement, then along with the CHANGE TRACKING OFF option, you must specify NO POPULATION. The index is only populated after the user executes the ALTER FULLTEXT INDEX command with the START FULL, INCREMENTAL, or UPDATE POPULATION clause.

Change Tracking Based Population

SQL Server maintains a record of the rows that have been modified in a table that has been set up for full-text indexing. These changes are propagated to the full-text index.

You start tracking changes by specifying the WITH CHANGE_TRACKING option in the CREATE FULLTEXT INDEX statement. When using change tracking, you can specify how the changes are propagated to the full-text index.

To automatically propagate changes to the full-text index, use the AUTO option in the CREATE FULLTEXT INDEX, or ALTER FULLTEXT INDEX statements.

By specifying the MANUAL option, the changes can be propagated manually, on a schedule, or by using the SQL Server Agent, or you can propagate them manually yourself.

Incremental timestamp-Based Population

Incremental population updates the full-text index for rows added, deleted, or modified after the last population, or while the last population was in progress. The requirement for incremental population is that the indexed table must have a column of the timestamp data type. If a timestamp column does not exist, incremental population cannot be performed. A request for incremental population on a table without a timestamp column results in a full population operation.

Incremental population requests are implemented as full populations if any metadata that affects the full-text index for the table has changed since the last population. This includes altering any column, index, or full-text index definitions.

At the end of a population, the SQL Gatherer records a new timestamp value. This value is equal to the largest timestamp value that the SQL Gatherer has seen. This value is what will be used when a subsequent incremental population starts.

See Also

Concepts

Full-Text Search Fundamentals

Other Resources

CREATE FULLTEXT INDEX (Transact-SQL)
ALTER FULLTEXT INDEX (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History