Difference between Delete and Truncate
Removing records from a table is a crucial operation. So I would like to pen down my knowledge about removing records from a table. There are two ways to do the same, Truncate and Delete.
I would like to explain the basic difference between two.
1. Delete is a DML whereas truncate is a DDL. Truncate deallocates the data pages for the table and does'nt delete them row by row. So if there is an Identity column in the table it would be reset to initial seed after truncate.
2. For Delete a Transaction Log is maintained. For this simple reason a Delete statement can be RolledBack and recovered using the transaction log.
On the other hand, for Truncate there is no entry in transaction log. So a truncate statement can't be recovered once committed. But we can always rollback a truncate statement if we trap it inside a TRANSACTION scope.
3. Where Clause can’t be used in truncate, so a truncate statement can't LOCK Rows.
DELETE * FROM TableName WHERE Clause