CODEWITHSUNDEEP
sql-serversql-server-2012rebuildfull-text-catalog
Matthew Baker
Matthew Baker

Reputation: 2729

Adding to full text catalogue without full rebuild

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.

Upvotes: 2

Views: 380

Answers (1)

Gareth Lyons
Gareth Lyons

Reputation: 1972

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

Upvotes: 3

Related Questions