about 9 years ago
The following article captures the difference between truncate and delete.
1) Rollback Possibility:
It is possible rollback a DELETE operation but not a TRUNCATE operation.
2) Impact on Identity
TRUNCATE resets identity of a table but DELETE does not.
3) Locking
DELETE statement is executed using a row lock, each row in the table is locked for deletion.
TRUNCATE always locks the table and page but not each row.
4) Use of trigger
DELETE activates a trigger because it is a row wise operation however triggers do not work with TRUNCATE.
5) Use of WHERE clause
We cannot use where condition in TRUNCATE however it is possible to use it with DELETE.
6) Performance
TRUNCATE is faster comapred to DELETE performance wise, because logs ae not maintained
You cannot use TRUNCATE TABLE on tables satisfy below conditions:
1) Are referenced by a FOREIGN KEY constraint.
2) Are part of an indexed view.
3) Are published by using transactional replication or merge replication.
For tables with one or more of these characteristics, use the DELETE statement instead.
0 Comment(s)