Sunday 27 September 2015

Difference between Delete and Truncate

Below list is major difference between Delete and Truncate in SQL Server;


Delete
Truncate
Command Type
It is DML command Type.
It is DDL command Type.
Filter
Specify the filter for command like where.
Can’t use Where clause.
Reset Identity
Delete command won’t reset the identity Column.
Truncate command  reset the identity Column.
Performance
Slower than truncate as it keeps logs
High performance  as it won’t keep log.
RollBack
 Rollback is possible.
Rollback is not possible.
Activate Trigger
Delete activates a trigger.
TRUNCATE cannot activate a trigger
Locked Behaviour
DELETE statement lock row, each row in the table is locked for deletion.
TRUNCATE always locks the table and page but not each row.


No comments:

Post a Comment