September 9, 2019 at 11:02 pm
I have an unusual raw data file that contains 65 different transaction types. Only one row has the key field and there are a lot of other things going on in the file. My strategy is to create a table for each transaction type, then consolidate them into a single table. After they are consolidated, I drop the 65 tables. Question. From a "fragmentation" perspective is it better to drop the tables or should I just truncate them? Any other concerns?
September 10, 2019 at 9:07 pm
From a fragmentation point of view, there's no difference. If you run a shrinkfile after either dropping or truncating those tables, you will have fragmentation, and the same fragmentation either way.
TRUNCATE TABLE will free the space consumed, but DROP TABLE will also drop the definitions. If you aren't using those tables after you're done, then drop them. If you will receive updates to the raw file and will perform this work down the road, then truncate may a better choice than drop, so you can re-use the tables.
How big are the tables and how long does the import take? On the surface, it looks like you should create those #tables in tempdb, and just let them go out of scope when you're done with them.
Eddie Wuerch
MCM: SQL
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply