Thursday, July 19, 2012

Difference between TRUNCATE and DELETE command in MS SQL Server

Sr. No. DLETE Command TRUNCATE Command
1. DELETE is a DML (Data Manipulation Language) command. TRUNCATE is a DDL (Data Definition Language) command.
2. DELETE command allows where clause. TRUNCATE does not allow WHERE clause.
3. We can ROLLBACK data in DELETE command. We cannot ROLLBACK data in TRUNCATE command. It removes all data permanently.
4. DELETE works on all table. TRUNCATE doesn’t work on that table which is referenced with another table.
5. DELETE Command is slower than TRUNCATE Command. TRUNCATE command is much faster than DELETE Command.
6. DELETE command doesn't resets any identity counter if there is any identity column present in the table . TRUNCATE resets identity counter if there is any identity column present in the table.
7. Triggers get fired in DELETE command if applied. A trigger doesn’t get fired in case of TRUNCATE Command.
8. DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically delete. TRUNCATE is also a logged operation but in a different way. TRUNCATE logs the de-allocation of the data pages in which the data exists. The de-allocation of data pages means that your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. This is what makes TRUNCATE a faster operation to perform over DELETE.

No comments:

Post a Comment