Adding to full text catalogue without full rebuild

Question

I have a product list in SQL Server with a large number of rows (100K +). We perform a regular full text search on the table to find products that contain a keyword. Upon adding a new product it is very slow to drop and recreate the full text catalogue to include the new part.

Is there any way I can add a single row to the full text catalogue without having to rebuild the entire catalogue?

This would need to be supported on SQL Server 2012 to current.

Answer 1

When creating a full text index within the catalog, you can specify the change_tracking option as auto (default), manual, or off. Auto will automatically propagate changes; manual requires you to run an alter index statement to update the index, and off does not track changes.

NB: updatetext & writetext operations are not picked up by change tracking.

You can check the change_tracking option for an index in the SSMS GUI or the sys.fulltext_indexes view.

You can change the setting for an index in SSMS or with an alter fulltext index... set change_tracking [option] statement.

https://msdn.microsoft.com/en-us/library/ms188359.aspx