Truncate or Delete

Posted: December 1, 2008 in Uncategorized

 

To truncate or to delete you can wrap any number of questions or statements around this.

  • I have seen this asked during Job interviews as what is the difference between Truncate and Delete.
  • I have seen postings on news groups and websites that question why data is not recoverable from the log after the truncate statement is issued.
  • I have been asked as to the fastest way to get rid of data that you will never need again.

The end result is that the Truncate Statement is pretty powerful. That is why basically you have to have DBO rights to the database to run it. There is some interesting answers to these questions. The standard or at least the most often that I have seen or heard is that the Truncate Table statement is a “non-logged” operation. Well there is some partial truth to this. The Truncate statement will not log each and every row like a delete statement.

Maybe an example will help here. Let’s say I have a table called Customers with 20 rows in it. If I were to run this statement:

Delete from Customers

All 20 rows in the Customers table will be gone. No different than If I run:

    Truncate Table Customers

However, In the delete statement each of those 20 rows will go into the Transaction Log. In the Truncate Table statement the page de-allocation is inserted into the transaction log. This may be more information then what you are looking for to just remove the rows, what you need to keep in mind is that if the table has 10 Million records in it your transaction log is going to grow by a large amount with the delete but with the truncate it will not.

Another difference between the two statement is that with the Delete Clause you can use a Where clause however with the Truncate Table you cannot. The reason goes back to what happens during a Truncate table statement is the pages are de-allocated. There is no logic other than does the page belong to the table. So if you only want to remove old customers from your table you need to use the delete statement, unless of course you want to lose the existing ones. Lets refer back to the table with 10 Million customers in the table, for example purposes only let’s say that 100,000 of them are active. If you are trying to get rid of all the customers that are not active an option would be to copy out the 100,000 Customers that are active and then Truncate the table. Put the Customers back in and you have completed your task.

Now the previous example has some loop holes that you would want to watch for…

If you have your database set with Foreign Keys that point to your Customer table then you will have to remove them before you run the truncate.

If your customer table has a identity field in it and you use the method that I described, you are going to be in some trouble… The identity will be reset to its seed value or 1 if you did not give it one upon the Truncate. A Delete statement will not remove that seed.

The big bonus to a Truncate statement is that since all it does is de-allocate the pages, it is very fast and very easy on the resources. So my rule of thumb I like to stick to is use a truncate when you need fast deletion on a table that you might as well drop and re-create. The Truncate saves you the time.    

Comments
  1. Brent Ozar says:

    I’d add one thing: people say that if you do a truncate, you can’t recover the table contents. That’s not true with today’s backup & recovery software – as long as your database is in full recovery mode and you’ve got the backup chain intact, you can still get your data back. Quest LiteSpeed, for example, can pull a single table’s contents right up to the moment before you truncated the table – all without restoring the database.

    I don’t like doing product plugs, but that one is so unknown and so valuable that I like pointing it out. It’s saved my bacon more than once when an ambitious DBA truncated a table to save time.

  2. chrisshaw says:

    Thanks Brent. Good Point, some other behavior that may be of some intrest is if you wrap your truncate in a Begin Trans and either Rollback and Commit you can also get the truncated data back. I am at the airport so I can not do the exact syntax now, but try it on a test table.

    Something along the lines of

    Begin Transaction
    Truncate Table test;

    Select count(*) from test

    Commit Transaction

    Select count(*) from test

  3. Isondart says:

    Great explanation on using truncation vs using delete. Keep them coming.

  4. […] Chris Shaw tells us the difference between Truncate or Delete. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s