Refresh index of full text search

Question

Hi, in SQL Server 2008, when setting up a full text search, it gives me an option to choose a time/day for it to re-populate the index, can I do this from code instead like C# code? if so how would I do it? Would I need to execute SQL code from C# to refresh the index?

Also, say if I DONT re-populate the index and add some new records, by using the FTS query will the new records still come up? But slower? Or will they not come up at all unless i reindex?

Answer 1

Edit: beware that there are two ways of keeping an FT index current:

• to "Update" it which requires keeping track of changes to individual records (this is done automatically for you, given the proper "Change Tracking" setting, but this does incur a slight overhead).
• to "re-popluate" it i.e. to create the complete index anew.

The former method is +/- transparent to continued service for the users of the underlying catalog, the latter implies a partial loss of service while the index is being repopulated (although maybe not in 2008?)

It is a bit unclear what type of update or re-population the question refers to, so the responses below are generic.

1) Yes! Full-text index re-population (or update) can be done programmatically using a plain TSQL query which looks like the following. Such queries can well be invoked from C# code, using OLEDB or even ODBC.

ALTER FULLTEXT INDEX ON myCatalog START START FULL POPULATION;    -- for full (re-)population
ALTER FULLTEXT INDEX ON myCatalog START UPDATE POPULATION;    -- for update

2) Yes, the FTS index will reflect changes to the database, in close to real-time, provided that it is setup accordingly.
You need to set the FT index for "Change tracking" so that it would be able to maintain the index current. This is the necessary condition. With the list of changed and new records in had (from the change tracking), SQL may either update the FT index "on the fly" (keeping this index slightly behind realtime re. the updates to the database; the delay is because the updates to FT index are a bit buffered, and also because this type of update is typically low priority with regards to CPU usage on the server), or this can be done "manually" (the manually is in quotes because "explicitly" may be more appropriate, for such manual updates may well be scheduled and hence happen "automatically".)

See this Microsoft technet article for more info. (Also check the links at the bottom of this article for an overview of FT indexes etc.)

Answer 2

thanks so much mjv, above, cant tick the answer for some reason though