0

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.

4
  • what version (2012, 2014) & edition (enterprise, standard) of sql server are you using ?
    – Kin Shah
    Apr 21, 2016 at 21:35
  • 1
    And what is the definition of the table including indexes? Apr 21, 2016 at 21:47
  • I presume you have no foreign keys reference table in question. Apr 22, 2016 at 7:19
  • 1
    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.
    – Molenpad
    Apr 22, 2016 at 10:30

1 Answer 1

0

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.

1
  • 3
    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. Apr 21, 2016 at 21:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.