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
WITH CHANGE_TRACKING OFF, NO POPULATION
To populate the index you need to execute the following statement:
ALTER FULLTEXT INDEX ON Production. ProductDescription START FULL POPULATION
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:
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.
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
WITH CHANGE_TRACKING OFF, NO POPULATION
To populate the index you need to execute the following statement:
ALTER FULLTEXT INDEX ON Production. ProductDescription START FULL POPULATION
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.