Effect of TRUNCATE TABLE different from DELETE on subsequent queries

Question

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?

Answer 1

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.