Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've had situations where there was a large feed I was taking in that basically overwrote a table in the db. This tabled information was queried a lot.

If Sql Server uses memory to store the queried data wouldn't it be better if I just updated rows instead of overwriting a table.

Is there a penalty for:

  • dropping a table and rewriting it
  • or just truncating it and pushing new rows into it
  • or focusing on updating only dirty rows?

What would be the preferred method?

share|improve this question
    
The biggest penalty is probably incurred by indexing and index recreation, how many indexes does the table have? –  NickW Mar 4 '13 at 10:02
    
in the last case it was the clustered primary key index –  Chris Mar 4 '13 at 10:03
1  
This is probably a case where you should spend a little time benchmarking, there are a lot of variables to consider, how many rows are updated/inserted, how large the indexes are, how the server indexes (after each row, or at the end of the update/insert), and so on. Machine specific values will count too, Memory, I/O, load.. –  NickW Mar 4 '13 at 10:13
    
I just want to point out that your third option is functionally different than the first two. Consider the case when the new table has less rows than the one you are overwriting. In the 3rd case the old rows would still be there. –  TTT Mar 5 '13 at 18:44
    
The consensus seems to be choose TRUNCATE over DROP: stackoverflow.com/questions/9103037/… –  TTT Mar 5 '13 at 18:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.