Saturday, July 31, 2010

What is difference Truncate Vs Delete?


In T-SQL there are two ways in which you can delete records, one using a DELETE statement and the other using TRUNCATE statement. Though the both the statements are same in the task carried out by them, yet there are significant differences between both the statements.

                    DELETE Statement
                                           TRUNCATE Statement
Removes rows one at a time and records an entry in the transaction log for each deleted row
Removes the data by de-allocating the data pages used to store the table data and records only the page de-allocations in the transaction log
Log of the rows deleted is maintained in a transaction
Log of the data page references is maintained in the transaction
Execution slow compared to Truncate statement due to row by row deletion
Execution is fast as only the reference to the data pages is deleted
Conditional removal of rows is possible
Conditional removal of rows is not possible
Identity seed is not reset
Identity seed is reset
Executed using a row lock, each row in the table is locked for deletion
Locks the table and page but not each row
DML Query
DDL Query



However DELETE and TRUNCATE Statements cannot be used when a table, 
  • is referenced by a foreign key constraint
  • participates in an indexed view

No comments:

Post a Comment


All Rights Reserved @ Raju Das