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?