question

Raj More avatar image
Raj More asked

Kill and Fill strategy

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?
ssissql-server-2008-r2etl
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
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.
5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sorry, badly phrased, stats are updated by truncate, rather by SELECT statements or other statements that would use them, but the stats would be marked for needing update, however if the very next statement is a drop index, chances are there's no issue.
1 Like 1 ·
If you consider your scenario, it would not make difference. If you are running TRUNCATE/dropping indexes, would the user be able to access the data?
1 Like 1 ·
I'd be fascinated to see some timings for the different approaches.
1 Like 1 ·
@Raj More In addition, you would have less maintenance to do, if you disable all indexes and then rebuild all indexes (like do not have to keep up to date definitions in the script). But as Mr. Grant Fritchey have said, test it thoroughly. @Grant Fritchey Sir, Please elaborate your statement "with TRUNCATE, then DROP INDEX, you could see the stats on the index will get updated automatically after the TRUNCATE". I thought the Truncate do not update the STATS. May be, I am wrong OR I am not able to understand your statement. So please accept my apology if this is the case. Thanks.
0 Likes 0 ·
@Usman Butt When I disable the Clustered access, it will deny access to the data. So I have to drop and create.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.