frequently dropping table in sql server

Question

Kindly help me understand my below concern.

In our current business requirement. We have to pull the complete data everytime. We have to truncate out table and load it with 20million of data.

I want to know instead of truncating. If I do a drop and load the table with select * into. Will this frequent dropping of table have any adverse affect on database performance in long term. Like high fragmentation or something related to pages.

Be noted that we cannot use merge as per our current data.

Just wanted to know the adverse affect of frequent drooping and creation of tables.

• Kin Shah: what version (2012, 2014) & edition (enterprise, standard) of sql server are you using ?
• Martin Smith: And what is the definition of the table including indexes?
• Colin 't Hart: I presume you have no foreign keys reference table in question.
• Molenpad: I can't see how dropping and recreating (then re-loading) a table will be any better than truncating and reloading. If you're saying you can't use merge to affect only changed and new rows, then your options are limited to what you are already doing. You have not mentioned any key constraints or any indexes involved, nor have you mentioned why you are looking to change your current process, so I'm going to go out on a limb and say, don't change anything.

Answer 1

There is a problem with the way you are doing this now, the huge amount of data just seems like a waste. Tons of logging and its just a nightmare for i.os. I would personally look into merge statements instead.

This performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

But if you are going with this solution, I would believe the better option is just to disable the indexes on the table. Then truncate the table and then load. This should allow faster inserts. You will need to reenable the indexes though and then rebuild them.

• Martin Smith: Truncate won't generate "Tons of logging". The insert...select into an empty table might be minimally logged depending on the recovery model of the database.