DELETE vs TRUNCATE – SQL Server

Although this is a very simple question that ‘what is the difference between using DELETE vs TRUNCATE statements in SQL Server’, & both are being used very frequently serving more or less the same purposes, but there are various aspects we should keep in mind before using them more effectively. And I am sure the below explanation would make you think before using DELETE vs TRUNCATE next time –which most of us do use one of these seeing as an alternate to the other.

Basic differences in terms of usage:

  1. TRUNCATE deletes the entire table data. DELETE can be used to delete either entire table data or selected table records by using WHERE clause
  1. TRUNCATE does reset the SEED value of Identity column to the default value (starting value) whereas DELETE doesn’t do that
  1. Truncate doesn’t invoke Triggers whereas Delete does
  1. Truncate is DDL (Data Definition Language) and Delete is DML (Data Manipulation Language)
  1. TRUNCATE TABLE cannot used be used when a foreign key references the table to be truncated. If all table rows need to be deleted using TRUNCATE and there is a foreign key referencing the table, you must drop the index and recreate it. However, DELETES just needs all the referenced rows/data to be removed first.

6. Truncates need db_owner and db_ddladmin permission.

In terms of Performance – internal working

  1. TRUNCATE is faster than DELETE – because Truncate needs locks on the table and schema but do not need locks on rows of the tables as in case of DELETE.

TRUNCATE TABLE is a statement that quickly deletes all records in a table by de-allocating the data pages used by the table. This reduces the resource overhead of logging the deletions, as well as the number of locks acquired; however, it bypasses the transaction log, and the only record of the truncation in the transaction logs is the page de-allocation. Records removed by the TRUNCATE TABLE statement cannot be restored. The unhooked pages/data will be removed synchronously or asynchronously (called as deferred de-allocation) based upon whether the data table is small enough or quite large respectively.

Whereas DELETE TABLE statements delete rows one at a time, logging each row in the transaction log, as well as maintaining log sequence number (LSN) information. Although this consumes more database resources and locks, these transactions can be rolled back if necessary.

A FEW MYTHS HERE……

Myth1: Truncate cannot be rolled back.

Fact: That’s not true. Truncate Table command is transactional. It can be rolled back – if it happens within an explicit transaction.

Let me reiterate it to illustrate the reality….TRUNCATE can be rolled back only if

  1. Explicit Transaction(s) are opened, i.e. by recording which pages and extents were de-allocated, there’s enough information to roll back, by just RE-allocating those pages later. And
  2. Current Session is not closed which means truncated data can’t be rolled back by means of the Log Files even if database is set to Full Recovery mode then.

On the other hand DELETE can always be rolled back.

A small question to be answered here is – How does SQL Server know not to reuse the pages that belonged to the table? It turns out the pages and/or extents involved are locked with an eXclusive lock, and just like all X locks, they are held until the end of the transaction.  And as long as the pages or extents are locked, they can’t be deallocated, and certainly cannot be reused.

Myth2: Truncate generate less log records. It depends. If table is small enough, truncating table will generate more logs.

Up to this point it’s fairly clear what should be used as per the requirement. Now, last but a substantial point also to be considered as explained below.

Till this point it seems TRUNCATE is a better statement over DELETE, but wait a minute. This may not always hold to be a true statement. Given a scenario where a large table is to be deleted for the existing records (may be done via Partition switch/merge etc) & at the same time data is being selected/inserted from/into the same table.

As stated above, Truncate statement takes an exclusive lock (perhaps app_lock_Mutex )on the entire table – and in case of the larger table, it is not accessible during that time – it results into a deadlock. And hence it is advisable to use DELETE, but perform the deletes into smaller batches.

Similar to this, sometimes deadlock is also encountered due to the following:

Each temporary table has an in-memory structure that contains a counter of all the pending transactions that operated on the table. When this counter decreases to 0, the temporary table is dropped in an autonomous transaction. However, the TRUNCATE TABLE statement does not increase the counter. Therefore, if the autonomous transaction tries to drop the temporary table before the transaction that runs the TRUNCATE TABLE statement commits, a deadlock occurs. The deadlock occurs between the autonomous transaction and the transaction that runs the TRUNCATE TABLE statement.

Advertisements
This entry was posted in SQL Server, SQL Server 2008. Bookmark the permalink.

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