CODEWITHSUNDEEP
sql-serversql-server-2008indexingfull-text-search
André Haupt
André Haupt

Reputation: 3524

SQL Server Full Text Index: When is the index updated?

We are using full text indexing on a table.

The default behaviour is to trigger an index update when a record in the source table is updated.

Would an index update be triggered if an update is done with the same values? Is SQL Server smart enough to know not to re-index this record.

Upvotes: 0

Views: 1476

Answers (1)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

http://msdn.microsoft.com/en-us/library/ms142575.aspx

"When change tracking is used, SQL Server maintains a record of the rows in the base table or indexed view that have been modified by updates, deletes, or inserts. Data changes through WRITETEXT and UPDATETEXT are not reflected in the full-text index, and are not picked up with change tracking."

Assuming that you are using (n)varchar as the column, then an UPDATE is an UPDATE, regardless of whether or not the value actually changed. Instead of using AUTO, you could do an Incremental Timestamp-Based Population of the index to reduce the overhead.

Upvotes: 2

Related Questions