Sunday, 27 September 2015

Difference between Delete and Truncate

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

Command Type
It is DML command Type.
It is DDL command Type.
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.
Slower than truncate as it keeps logs
High performance  as it won’t keep log.
 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