Good Article on Full Text Search , Change tracking on full textsearch

http://sql-articles.com/articles/dba/sql-server-2005-full-text-search/

Populating full text index:

Creating and maintaining a full-text index involves populating the index by using a process called a population (also known as a crawl). SQL Server supports the following types of population: full population, change tracking-based automatic or manual population, and incremental timestamp-based population.

Full population:

Firstly, whenever we create a full text index there will be a full population of the index for all the rows. To avoid it we need to use the option CHANGE_TRACKING OFF, NO POPULATION. Those options will turn off change tracking and will prevent populating the full text index while FTX creation. Since the Full population consumes a lot of resources we need to do them at offproduction hours. During a full population, index entries are built for all the rows of a table or indexed view. A full population of a full-text index builds index entries for all the rows of the base table or indexed view.

Change tracking based population: There are 2 types

a.) Using Automatic whenever an insert/update or delete happens to the full text index there will be a population of those rows alone (automatically)

b.) Using Manual whenever DML operations happen it will not get propagated automatically to the FTX. Hence we need to use automatic population so that whenever a change happens in the text field it will immediately be available in the full text index i.e. it will be populated instantaneously. If we use manual population we need to run the command using a job to populate the changes alone manually.

The overhead involved here is the SQL Server will maintain a table to track the list of tables and rows modified.

Incremental population: We need to have a timestamp column in the table in order to make use of Incremental population. This will populate only the rows that have modified since the previous incremental population. This method is highly suitable for tables which change frequently.

The time delay for the full text index data to be available while searching after getting populated is close to 1 to 2 minutes maximum. Without getting populated it will not be available.