Archive for December 1, 2008

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.