I am using a kill-and-fill strategy in my ETL for the staging tables. The tables involved contain 8M, 18M and 37M rows respectively. One has a Clustered Index and all have multiple Non-Unique, Non-Clustered indexes. Is there any difference between the following approaches to the kill-and-fill: - `TRUNCATE - DROP INDEX - FILL DATA - CREATE INDEX` - `DROP INDEX - TRUNCATE - FILL DATA - CREATE INDEX` Are there any reasons to choose one over the other?
Well, possibly, the first option, with TRUNCATE, then DROP INDEX, you could see the stats on the index will get updated automatically after the TRUNCATE. So, the second option which drops the index first might be more efficient. TRUNCATE isn't helped by the index, so dropping it first is fine. But, to be 100% sure, you'd want to test it.