Pros & Cons of TRUNCATE vs DELETE FROM

Question

Could someone give me a quick overview of the pros and cons of using the following two statements:

TRUNCATE TABLE dbo.MyTable

vs

DELETE FROM dbo.MyTable

It seems like they both do the same thing when all is said and done; but are there must be differences between the two.

Solution 1

TRUNCATE doesn't generate any rollback data, which makes it lightning fast. It just deallocates the data pages used by the table.

However, if you are in a transaction and want the ability to "undo" this delete, you need to use DELETE FROM, which gives the ability to rollback.

EDIT: Note that the above is incorrect for SQL Server (but it does apply to Oracle). In SQL Server, it is possible to rollback a truncate operation if you are inside a transaction and the transaction has not been committed. From a SQL Server perspective, one key difference between DELETE FROM and TRUNCATE is this: "The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted 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."

In other words, there is less logging during a TRUNCATE because only the page deallocations are recorded in the transaction log, whereas with a DELETE FROM each row deletion is recorded. That's one of the reasons TRUNCATE is lightning fast.

Note also from that MSDN link that you cannot truncate tables that are referenced by foreign key constraints, participate in an indexed view, or are published by using transactional replication or merge replication.

EDIT 2: Another key point is that TRUNCATE TABLE will reset your identity to the initial seed, whereas DELETE FROM will carry on incrementing from where it left off. Reference: Ben Robinson's answer.

Solution 2

Another key point not mentioned in the other answers is that TRUNCATE TABLE will reset your identity to the initial seed, whereas DELETE FROM will carry on incrementing from where it left off.

Solution 3

Another difference from a security perspective is that TRUNCATE requires ALTER privileges on the table, while DELETE merely requires (drum roll) DELETE permissions on that table.

Solution 4

TRUNCATE TABLE doesn't log the transaction. That means it is lightning fast for large tables. The downside is that you can't undo the operation.

DELETE FROM logs each row that is being deleted in the transaction logs so the operation takes a while and causes your transaction logs to grow dramatically. The upside is that you can undo the operation if need be.

Solution 5

Outline of Delete Vs Truncate in SQL server

For Complete Article take after this connection: Delete Vs Truncate in SQL Server

/*Truncate - Syntax*/
TRUNCATE TABLE table_name

/*Delete - Syntax*/
DELETE FROM table_name
WHERE some_condition

View more solutions

Comments

[nothrow]

TRUNCATE may also break consistency (=doesn't check for foreign keys, and doesn't fire triggers)

[dcp]

@Yossarian - According to MSDN: "You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause". msdn.microsoft.com/en-us/library/aa260621%28SQL.80%29.aspx

[nvogel]

TRUNCATE is logged and can be undone just like DELETE - by using ROLLBACK or RESTORE.

[ScaleOvenStove]

only if you use transactions, and if the truncate is commited, it cannot be rolled back. it is DDL and not logged to log file.

[Dhananjay]

truncate can alos be rolled backed. sqlblog.com/blogs/denis_gobo/archive/2007/06/13/1458.aspx

[dcp]

@Dhananjay - Not always: blog.sqlauthority.com/2007/12/26/…

[Dhananjay]

@dcp: yes. Thats true. I was telling that "truncate cann't be rolled back" is not 100% true statement.

[Florin Dumitrescu]

It is interesting that both this reply and the one marked as answer tell almost the same thing, but this has been downvoted to -1 while the one marked as answer upvoted to +7 (at least at the moment I am writing this). As @dcp previously noted, this is a good article that describes the rollback behavior of TRUNCATE vs DELETE.

[khalid khan]

If You want to apply truncate on a table referenced by a Foreign key then you can also do it by removing the Foreign key Constraint and then truncate and then reapply the Foreign key constraint.

[nvogel]

This accepted answer is wrong. TRUNCATE is a fully logged operation and can be rolled back just like DELETE or any other operation. The perceived "lightning fast" operation of TRUNCATE is due to deferred (asynchronous) logging not due to a lack of "rollback data". The "sqlauthority" article mentioned is misleading but the comments on that blog do point out that TRUNCATEd data can be restored from a point-in-time restore of the log file, just as DELETEs can be.

[dcp]

@sqlvogel - Yes, you are right about TRUNCATE being able to be rolled back, thanks for noting this. I have edited the answer. However, I think the "lightning fastness" is due to just data pages being logged instead of every row being logged, which results overall in much less logging. Not sure about the asynchronous part since MSDN didn't mention that. Also, I don't think TRUNCATE can be restored from the log file after the transaction has been committed unless those data pages haven't been reallocated.

[nvogel]

MSFT calls it Deferred Drop. Page deallocations are logged after the truncate is committed rather than before. This doesn't compromise t-log backup/restore and a log restore is always preceded by a database restore anyway.

[nvogel]

Conceivably there may be edge cases where recovery of data is possible from a delete but not from a truncate. I doubt that's an "official" supported scenario though (possibly it might have to involve 3rd party tools or unsupported techniques). The sqlauthority article doesn't explain any of this. Without further info I'm sceptical about the claim that truncated data is not always recoverable (under full recovery model I mean).

[dcp]

@sqlvogel - Thanks for the link on Deferred Drop, I did not know about that. As for the edge cases, I think if the data pages that are freed as a result of the truncate are reallocated to some other table and then written to, then one wouldn't be able to recover them in a restore operation. I would equate it with accidentally deleting a partition on a hard drive. The old data is still "there", until you start overwriting it with new stuff, so it's sometimes possible to recover the partition if you act quickly. Anyway, great discussion.

[nvogel]

@dcp When you restore to an earlier point in time every allocated page in the database gets restored regardless of its prior state. What happens during and after truncate doesn't matter because the truncated data will always be contained either in the database backup, log backup(s) or the tail portion of the log.

[dcp]

@sqlvogel - Yes, under full recovery mode you'd have a full backup of all those pages so that restore would work fine. But if you only had differential backups, I think you'd have to use some tool like this (solutioncenter.apexsql.com/…) to try to get to those data pages. That's the scenario I was thinking of when I referred to the deallocated data pages. I would imagine these tools would try to reconstruct those data pages somehow using the log file.

[nvogel]

True enough. If your full database backups go up in smoke (or you never made any!) then you will not be in a happy place, third party tools or no third party tools. It seems like a stretch to say that's a disadvantage/limitation of TRUNCATE though because the same could apply equally to other DML operations - DELETE included. If you only have differential backups and the tail log from which to recover data then many/most committed changes will be totally unrecoverable.