Guest Posts

Performance Myths : Truncate Can't Be Rolled Back

SentryOne Newsletters

The SQLPerformance.com bi-weekly newsletter keeps you up to speed on the most recent blog posts and forum discussions in the SQL Server community.

eNews is a bi-monthly newsletter with fun information about SentryOne, tips to help improve your productivity, and much more.

Subscribe

Featured Author

Jonathan Kehayias is a Principal Consultant with SQLskills and the youngest MCM ever.

Jonathan’s Posts

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:

“TRUNCATE TABLE is not logged and therefore cannot be rolled back. You have to use DELETE, if in a transaction.”

Reading the manual

The Books Online article on TRUNCATE TABLE is fairly descriptive:

“Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.”

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:

Rows in the table before and after a TRUNCATE / 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.

Log records written after a TRUNCATE (240)

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);

Log records written after a DELETE (440K+)

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

Guest Author : Derik HammerDerik 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.