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.
I agree, you have to find tipping point for your system.
What I have seen in our Data Warehouse is that the Fact tables with few indexes and thus having index space smaller than data space load faster with drop/recreate during the ETL.
On the other hand for Fact tables with lot of indexes and thus having index space greater than data space, ETL is faster if indexes are not dropped. We do rebuild these indexes monthly though to take care of fragmentation.
So sometimes you have to use both strategies in your system; drop/recreate and leaving the indexes alone during ETL. Keeping track of it does adds to maintenance list though!