Difference between TRUNCATE and DELETE command
Lets see the difference between DELETE and TRUNCATE command as below
DELETE |
TRUNCATE |
|
DML Statement |
DDL Statement |
|
Data Rollback is possible, all the row by row data deletion will be recorded in transaction log |
Rollback is possible for entire table and not at row level, since data page reallocation will be logged in transaction log Eg.) BEGIN TRAN TRUNCATE Table Tablename ROLLBACK |
|
No Commit is performed neither before nor after. (Because it is a DML Statement) |
It issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible. (Because it is a DDL Statement) |
|
Row level locking will happen while performing deletion |
Table level locking will be happen while performing truncation |
|
A delete does not move the High Water Mark of the table back to zero, it retains its original position. |
A truncate moves the High Water Mark of the table back to zero |
|
WHERE statement can be included |
WHERE statement cannot be included |
|
Activates DML trigger |
Doesn’t activate DDL trigger |
|
Able to execute the command on the table that contains foreign key |
Not able to execute the command on the table that contains foreign key |
|
Consume more resources since need to process row by row and all these are logged operation |
Consume less resource since it will process the full table and its non-logged operation |
Lets discuss the above points using an example