SQL Server Urban Legend: You can not rollback a TRUNCATE statement

It's truly amazing how some really silly beliefs on SQL Server keep on coming back in discussions. Everybody continues to believe them, yet it's so easy to proof they're wrong. One of these 'urban legends' is the following: You can not rollback a TRUNCATE statement.

Every urban legend has a little grain of truth: indeed, one of the reasons why a TRUNCATE is faster than a DELETE has to do with the transaction log. That's the file containing all information to rollback your transactions properly. While a DELETE statement sends all the deleted records one-by-one to the transaction log, the TRUNCATE only saves the references to the deallocated pages. That's a lot less data, but still more than enough information for restoring the data when a rollback is encountered.

[For the sake of completeness: other reasons why a TRUNCATE is faster than a DELETE include bypassing constraints and triggers.]

Anyway, here's a little script to debunk the myth:

/****************************************************************/
/*              Microsoft SQL Server Urban Legends              */
/****************************************************************/
/*                                                              */
/* Urban Legend: "A TRUNCATE statement can not be rolled back." */
/*                                                              */
/****************************************************************/
 
IF OBJECT_ID('tempdb..#ProductTotals','U') IS NOT NULL
  DROP TABLE #ProductTotals
GO
 
CREATE TABLE #ProductTotals
(
   ProductID INT 
  ,Revenue   MONEY
)
 
  INSERT 
    INTO #ProductTotals (ProductID, Revenue)
  SELECT ProductID, SUM(UnitPrice * OrderQty)
    FROM AdventureWorks.Sales.SalesOrderDetail
GROUP BY ProductID
 
BEGIN TRANSACTION 
 
    TRUNCATE TABLE #ProductTotals
    -- DELETE FROM #ProductTotals   /* Same result, but slower. */
 
ROLLBACK TRANSACTION
 
-- IF > 0 THEN BUSTED
SELECT COUNT(*) AS [Number of rows]
  FROM #ProductTotals
 
/****************************************************************/
/* Status: BUSTED!!!                                            */
/****************************************************************/

2 comments:

  1. BlueHost is ultimately the best hosting company for any hosting plans you need.

    ReplyDelete
  2. Quantum Binary Signals

    Get professional trading signals delivered to your cell phone every day.

    Start following our signals right now & profit up to 270% per day.

    ReplyDelete