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