Wednesday, 28 October 2015

Difference between Delete and Truncate in SQL?

Delete
Truncate
Delete is DML (Data Manipulation Language) command.
Truncate is DDL (data Definition Language) command.
Delete statement is executed using a row lock.
Truncate table always lock the table and page but not each row.
We can specify in where clause.
We cannot specify in where clause.
It delete specified data if where condition exists.
It deletes all the data of table.
Delete activates a trigger because the operation is logged individually.
Truncate cannot activate a trigger because operation does not log individually row delete.
Rollback is possible in Delete.
Rollback is not possible in Truncate.
Slower then Truncate because it keep log.
Faster in performance, Don’t keep logs.

No comments:

Post a Comment