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.
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.