Performance Myths : Truncate Can't Be Rolled Back

Guest Author : Derik Hammer (@SQLHammer)

 
The differences between TRUNCATE TABLE and DELETE are often misunderstood. I seek to disprove the myth that TRUNCATE TABLE cannot be rolled back:

Reading the manual

The Books Online article on TRUNCATE TABLE is fairly descriptive:

The fact that TRUNCATE TABLE uses fewer transaction log resources implies that it does write to the transaction log to some degree. Let's find out how much and investigate its ability to be rolled back.

Prove it

In an earlier post, Paul Randal goes through this in meticulous detail, but I thought it would be useful to provide very simple repros to disprove both elements of this myth.

Can TRUNCATE TABLE be rolled back?

Proving that TRUNCATE TABLE can be rolled back is easy enough. I will simply put TRUNCATE TABLE in a transaction and roll it back.

USE demo;
BEGIN TRANSACTION;
  SELECT COUNT(*) [StartingTableRowCount] FROM [dbo].[Test];
  TRUNCATE TABLE [dbo].[Test];
  SELECT COUNT(*) [TableRowCountAfterTruncate] FROM [dbo].[Test];
ROLLBACK TRANSACTION;
SELECT COUNT(*) [TableRowCountAfterRollback] FROM [dbo].[Test];

There are 100,000 rows in the table and it returns to 100,000 rows after rollback:

Does TRUNCATE TABLE write to the log?

By executing a CHECKPOINT, we get a clean starting point. Then we can check the log records before and after the TRUNCATE TABLE.

USE demo;
CHECKPOINT;

  SELECT COUNT(*) [StartingLogRowCount]
  FROM sys.fn_dblog (NULL, NULL);

  TRUNCATE TABLE [dbo].[Test];

  SELECT COUNT(*) [LogRowCountAfterTruncate]
  FROM sys.fn_dblog (NULL, NULL);

Our TRUNCATE TABLE command generated 237 log records (at least initially). This is what enables us to perform a rollback, and how SQL Server registers the change to begin with.

What about DELETEs?

If both DELETE and TRUNCATE TABLE write to the log and can be rolled back, what makes them different?

As mentioned in the BOL reference above, TRUNCATE TABLE takes fewer system and transaction log resources. We already observed that 237 log records were written for the TRUNCATE TABLE command. Now, let us look at the DELETE.

USE demo;
CHECKPOINT;

  SELECT COUNT(*) [StartingLogRowCount]
  FROM sys.fn_dblog (NULL, NULL);

  DELETE FROM [dbo].[Test];

  SELECT COUNT(*) [LogRowCountAfterDelete]
  FROM sys.fn_dblog (NULL, NULL);

With over 440,000 log records written for the DELETE, the TRUNCATE command is clearly much more efficient.

Wrap-up

TRUNCATE TABLE is a logged command and can be rolled back, with a huge performance advantage over an equivalent DELETE. DELETE becomes important when you want to delete fewer rows than exist in the table (since TRUNCATE TABLE does not accept a WHERE clause). For some ideas about making DELETEs more efficient, see Aaron Bertrand's post, "Break large delete operations into chunks."

About the Author

Derik is a data professional and freshly-minted Microsoft Data Platform MVP focusing on SQL Server. His passion focuses around high availability, disaster recovery, continuous integration, and automated maintenance. His experience has spanned long-term database administration, consulting, and entrepreneurial ventures working in the financial and healthcare industries. He is currently a Senior Database Administrator in charge of the Database Operations team at Subway Franchise World Headquarters. When he is not on the clock, or blogging at SQLHammer.com, Derik devotes his time to the #sqlfamily as the chapter leader for the FairfieldPASS SQL Server user group in Stamford, CT.