I always thought of TRUNCATE TABLE as an equivalent of DELETE, except that:

1) TRUNCATE can be used only in some cases (no FK referencing table, user must have ALTER permission etc)

2) TRUNCATE is faster to execute

Apart from that, I expected them to have identical results. But from my experience, it seems that a TRUNCATE improves the performance of subsequent queries over a DELETE. I have a script that clears some tables, then fills them with millions of rows from another database on the same server, like:

INSERT NEWDB..newtable(a,b,c) SELECT a,b,c FROM OTHERDB..oldtable

Switching the clearing of tables from DELETE to TRUNCATE TABLE made the deletion much faster (as obvious) but also made the subsequent data copying at least 10 times faster. Interesting to note, the recovery model for the database is SIMPLE, so it is not a matter of logs. Can someone explain why this is happening? Or maybe I am missing something and the gain in speed is caused by some other factor?

  • 1
    Are your tables HEAPs? – sp_BlitzErik Apr 11 '17 at 9:59
  • On a related note, the LOG is used regardless of which recovery model you are using. The difference is in how long the transactions stay in the LOG. In the event that the server needs to roll the transaction back, it has to come from somewhere. SIMPLE will free the space in the log file at the next checkpoint, while FULL/BULK wait until the next log backup. TRUNCATE is a minimally logged command, requiring ALTER permissions as you noted while a DELETE has to write the modified pages to the LOG file, even if it's in SIMPLE. – Jonathan Fite Apr 11 '17 at 13:21

OTHERDB..oldtable --> the data could be in buffers from the first run.

Try running

DBCC DROPCLEANBUFFERS --Not in production though !!

and then your insert query.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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