A data-warehouse table with about 18M rows has a clustered index on it. This table is truncated and updated every day (We build day-over-day comparisions)
Since all the data has gone, do I need to REBUILD the Clustered Index before I start filling the table up with data?
In other words, is there any unwanted residue left after the table is truncated that I need to get rid of?
asked Dec 17 '11 at 03:49 AM in Default
I agree that it wouldn't make sense to rebuild the clustered index after the truncate. The table is empty at that point, and so is the index.
Something you do have to consider though, is the fact that the clustered index will have to be updated for each record (or batch of records) you isert into the table. That is a pretty expensive exercise, and you could possibly improve the performance of the process significantly by either disabling or removing the clustered index before loading...and then rebuilding it after all the data has been loaded.
answered Dec 21 '11 at 07:12 AM
Rebuilding the clustered index before doesn't make sense: the table is empty. It's just that truncating is resetting the high-water mark instead of deleting records. But the result is the same.
answered Dec 17 '11 at 07:38 AM
Wilfred van Dijk