Andy Warren (7/22/2008)
Have to find the tipping point. Drop/create may or may not be faster, for example if you are adding 1 million rows to a 100 million row table. The other portion of the trade off is fragmentation, by rebuilding at the end you correct any fragmentation created during load. Either way you should update column based stats (those that dont match to an index) post load.
Definitely have to find the tipping point - and determine what is best for your process. However, dropping and recreating the indexes is actually a lot more work than disabling/rebuilding.
As another poster pointed out - it is very easy to build a procedure that loops through all non-clustered indexes and disable them. This allows for adding new indexes, dropping existing indexes, etc... as you need them without having to modify your code at all.
To rebuild the indexes at the end of the process, you issue a single command for the table:
ALTER INDEX ALL ON table REBUILD;
This rebuilds all indexes for the table, including the clustered index. I have a table on my report server with well over 120 million rows and multiple indexes. The rebuild of all indexes on this one table takes no more than 30 minutes, which is far less time than the increased processing time if I leave the indexes enabled.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs