Populating the Full-Text Index in Search: SQL Server

July 20, 2014 , , , , 0 Comments

After creating the full-text index, you need to populate it with the data in the columns enabled for full-text support. The SQL Server full-text search engine populates the full-text index through a process called population. Population involves filling the index with words and their location in the data page. When a full-text index is created, it is populated by default. In addition, the SQL Server automatically updates the full-text index as the data is modified in the associated tables.

However, the SQL Server does not keep a list of changes made to the indexed data when the CHANGE_TRACKING option is off. This option is specified while creating the full-text index by using the CREATE FULLTEXT INDEX statement.

If you do not want the full-text index to be populated when it is created using the CREATE FULLTEXT INDEX statement, then you must specify NO POPULATION along with the CHANGE TRACKING OFF option. To populate the index, you need to execute the ALTER FULLTEXT INDEX command along with the START FULL, INCREMENTAL, or UPDATE POPULATION clause.

For example, to create an empty full-text index on the ProductDescription table, you can execute the following statement:

CREATE FULLTEXT INDEX ON Production. ProductionDescription (Description)
KEY INDEX PK_ProductDescription_ProductDescriptionID

To populate the index you need to execute the following statement:

The preceding statement will populate the full-text index created on the ProductDesctiption table.

Similar to regular SQL indexes, full-text indexes can also be updated automatically as the data is modified in the associated tables. This repopulation can be time-consuming and adversely affect the usage of resources of the database server during periods of high database activity. Therefore, it is better to schedule repopulation of full-text indexes during periods of low database activity. You can specify the following types of full-text index population methods to repopulate the index:

Full Population

You can use this method when you need to populate the full-text catalog or the full-text index for the first time. After that, you can the maintain the indexes by using change tracking or incremental populations.
During a full population of a full-text catalo, 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.

Change Tracking-Based Population

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

Incremental Timestamp-Based Population

The incremental population method updates the full-text index with the data that has been changed since the last time the index was refreshed. For an incremental population refresh to work, the indexed table must have a column of the timestamp data type. If a table does not have a column of the timestamp data type, then only a full population refresh can be done.

Rhett Butler

Some say he’s half man half fish, others say he’s more of a seventy/thirty split. Either way he’s a fishy bastard. Google