Can We TRUNCATE a Table Inside a Transaction and ROLLBACK It

 TRUNCATE TABLE in SQL Server is a metadata-driven operation that deallocates the data pages associated with a table instead of deleting rows individually. Although it is minimally logged, the page deallocation records and allocation metadata changes are written to the transaction log. Because these changes are transactional, SQL Server can undo them during rollback by restoring the original allocation structures. Therefore, when executed inside an explicit transaction, TRUNCATE TABLE can be safely rolled back until the transaction is committed. This provides the performance benefits of minimal logging while preserving ACID transaction guarantees.

TRUNCATE is minimally logged, not unlogged.

Let’s see the example

Here we are creating a table and inserting few records

CREATE TABLE dbo.TestTable (     ID INT ); INSERT INTO dbo.TestTable VALUES (1),(2),(3),(4),(5);  SELECT * FROM dbo.TestTable;

Now we are truncating this table in transaction

BEGIN TRANSACTION; TRUNCATE TABLE dbo.TestTable; SELECT COUNT(*) AS RecordsAfterTruncate FROM dbo.TestTable;

Table truncated successfully. See the records.

Now we are going to Rollback this transaction

ROLLBACK TRANSACTION; SELECT *  FROM dbo.TestTable;

Data is rollbacked.

Why Rollback Is Possible

When SQL Server truncates a table:

BEGIN TRAN;

TRUNCATE TABLE Sales;

The pages are not immediately destroyed.

SQL Server maintains information about:

Ø  Which pages were deallocated

Ø  Which allocation units belonged to the table

If: ROLLBACK;

occurs, SQL Server:

Ø  Reads the transaction log.

Ø  Finds the deallocation operations.

Ø  Reverses them.

Ø  Reattaches the pages.

The rows become visible again.