3

I want to schedule a Full Text Index's population to keep it more up to date rather than leaving it to Automatic and not knowing the next population time as the users are complaining about the data they enter would not show up until too long a time has passed.
For this, I right click on the table on which Full Text Index is defined and go to Schedules. It asks to choose the "Population Type". "Table-Incremental" and "Table-Update" are two of the options. I don't want to go with the "Table-Full" option as it might consume a lot of resources.
What is the difference between these two ("Table-Incremental" and "Table-Update") options?
Thank you.

2

By default the Full-Text Engine uses automatic population on a full-text index and changes are tracked as data is modified in the base table, and the tracked changes are propagated automatically. The full-text index is updated in the background.

For incremental population your table must have a column of the timestamp data type. At the end of a population, the Full-Text Engine records the largest timestamp value and will use that for next incremental population

Table-Update will use change tracking to maintain a full-text index after its initial full population. The SQL Server will then maintain a record of the rows in the base table or indexed view that have been modified by updates, deletes, or insert and will use this information to populate the Full Text Index. This comes with an overhead.

In most cases you are better off using Automatic updates, but if you have a table that's frequently updated and you have a timestamp column a manual Table-Incremental can help. (https://technet.microsoft.com/en-us/library/ms142575%28v=sql.100%29.aspx)

  • Can you please elaborate on which one to use when? Your reply is too bookish to infer a decision and act upon.Thanks. – Stackoverflowuser May 27 '15 at 13:17
  • You must have a column of the datatype timestamp to be able to use a incremental population. That's pretty clear, right ? If you don't have a timestamp column you have to use Table-Update, which will use change tracking and add extra load on the database. In both cases you will be able to tell your users that the FullText search will update within x minutes. – Spörri May 27 '15 at 13:26
  • 2
    The table doesn't have a timestamp column defined but right clicking on the table - > "Full Text Index" shows an option to "Start Incremental Population" - and indeed it completes successfully. How is that possible? – Stackoverflowuser May 27 '15 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.