Difference between Delete and Truncate?
TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. (1) But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log. (2) Because TRUNCATE TABLE is not logged, it cannot activate a trigger. (3) The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. Of course, TRUNCATE TABLE can be rolled back. 94.
TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. (1) But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log. (2) Because TRUNCATE TABLE is not logged, it cannot activate a trigger. (3) The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. Of course, TRUNCATE TABLE can be rolled back.
