Difference between Truncate and Delete in SQL

Truncate
Delete
TRUNCATE table does not maintain such ROLLBACK tablespace therefore it does not return data after ROLLBACK
Delete maintains a ROLLBACK tablespace which stores data which is deleted, so when you execute ROLLBACK data is gets back.
Truncate is a DDL command.
Delete is a DML command.
We can not specify filter in WHERE clause.
We can specify filter in WHERE clause.
Truncate deletes all row from Table.
Delete deletes row based on WHERE condition or can delete whole table.
Truncate is faster as compared to delete
Delete is slower as compared to Truncate
No log is maintained for Truncate for rows, Log is maintained for data pages
log is maintained for Delete for each row
Truncate can not activate a trigger.
Delete can activate trigger.
Resets the identity column.
It does not reset the identity column.
Difference between Truncate and Delete in SQL Difference between Truncate and Delete in SQL Reviewed by NEERAJ SRIVASTAVA on 11:07:00 PM Rating: 5

No comments:

Powered by Blogger.