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!!!                                            */

/****************************************************************/