I'm on SQL Server 2008 and have a table, for reporting purposes, with 500,000 records that will easily reach the millions. The table will employ a full text index for rapid searching on a handful of columns.
As this is a reporting table and not the source transactional table, sometimes new records will be added, and other times existing records will have to be removed due to changes going on in the source table.
My question is in regards to the best way to build (ongoing) the reporting table and maintain the full text index for this table.
Once the full text index is added, should I:
- leave the index alone, and delete/add records as appropriate
- leave the index alone, truncate the reporting table, and then insert all appropriate records
I've come across these articles so far while researching, but best practice for this scenario is not readily apparent.