I know this has been done many times, but still here is something from my side. What is the difference between DELETE and TRUNCATE?
Well one reason I wanted to write this post was, on so many of the blogs, forum threads I keep seeing statements like “TRUNCATE cannot be rolled back”. This is also one of the most frequently asked questions in Interviews. And since my target audience is people who are just started learning SQL (and NOT SQL Experts), I thought I should write this one.
Instead of just focusing on ROLLBCAK I will try to cover all the differences between DELETE and TRUNCATE.
DELETE - http://msdn.microsoft.com/en-us/library/ms189835.aspx
TRUCNATE - http://msdn.microsoft.com/en-us/library/ms177570.aspx
Well one reason I wanted to write this post was, on so many of the blogs, forum threads I keep seeing statements like “TRUNCATE cannot be rolled back”. This is also one of the most frequently asked questions in Interviews. And since my target audience is people who are just started learning SQL (and NOT SQL Experts), I thought I should write this one.
Instead of just focusing on ROLLBCAK I will try to cover all the differences between DELETE and TRUNCATE.
- Remove Data : First thing first, both can remove the data from a table.
But a DELETE can be used, to remove the rows not only from a Table but also from a VIEW or the result of an OPENROWSET or OPENQUERY subject to provider capabilities.
- FROM Clause : With DELETE you can also delete rows from one table/view/rowset_function_limited based on rows from another table by using another FROM clause. In that FROM clause you can also write normal JOIN conditions. Actually you can create a DELETE statement from a SELECT statement that doesn’t contain any aggregate functions by replacing SELECT with DELETE and removing column names.
With TRUNCATE you can’t do that.
- WHERE : A TRUNCATE cannot have WHERE Conditions, but a DELETE can. That means with TRUNCATE you can’t delete a specific row or specific group of rows.
TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause.
- Performance : TRUNCATE TABLE is faster and uses fewer system and transaction log resources.
And one of the reason is locks used by either statements. The DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row.
- Transaction log : DELETE statement removes rows one at a time and makes individual entries in the transaction log for each row.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
- Pages : After a DELETE statement is executed, the table can still contain empty pages.
TRUNCATE removes the data by deallocating the data pages used to store the table data.
- Trigger : TRUNCATE does not activate the delete triggers on the table. So you must be very careful while using TRUNCATE. One should never use a TRUNCATE if delete Trigger is defined on the table to do some automatic cleanup or logging action when rows are deleted.
- Identity Column : With TRUNCATE if the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value 1 is used.
DELETE doesn’t reset the identity counter. So if you want to retain the identity counter, use DELETE instead.
- Replication : DELETE can be used against table used in transactional replication or merge replication.
While TRUNCATE cannot be used against the tables involved in transactional replication or merge replication. - Rollback : DELETE statement can be rolled back.
TRUNCATE can also be rolled back provided it is enclosed in a TRANSACTION block and session is not closed. Once session is closed you won't be able to Rollback TRUNCATE.
- Restrictions : The DELETE statement may fail if it violates a trigger or tries to remove a row referenced by data in another table with a FOREIGN KEY constraint. If the DELETE removes multiple rows, and any one of the removed rows violates a trigger or constraint, the statement is canceled, an error is returned, and no rows are removed.
And if DELETE is used against View, that View must be an Updatable view.
TRUNCATE cannot be used against the table used in Indexed view.
TRUNCATE cannot be used against the table referenced by a FOREIGN KEY constraint, unless a table that has a foreign key that references itself.
DELETE - http://msdn.microsoft.com/en-us/library/ms189835.aspx
TRUCNATE - http://msdn.microsoft.com/en-us/library/ms177570.aspx
I hope you liked my this post on the topic: the difference between DELETE and TRUNCATE in SQL Server.
