Can TRUNCATE be rolled back in SQL Server?

A database transaction, by definition, must be atomic (it must either complete in its entirety or do not affect whatsoever), consistent, isolated, and durable. These four properties are commonly referred as ACID.

The misconception about the TRUNCATE command is that it cannot be rolled back. But it is a completely false statement and misunderstood by many. The following example demonstrates the behavior of DELETE and TRUNCATE when the transaction is rolled back.

DROP TABLE IF EXISTS [dbo].[customer]
CREATE TABLE [dbo].[customer] (CustomerId INT, PrimaryPhone VARCHAR (12))
GO
INSERT INTO [dbo].[customer] (CustomerId, PrimaryPhone) 
VALUES (1,'214-456-3450'), (2,'914-585-5896'), (3,'234-758-2536')
GO

BEGIN TRANSACTION 
	DELETE [dbo].[customer]
ROLLBACK TRANSACTION 
SELECT * FROM [dbo].[customer]
GO

BEGIN TRANSACTION 
	TRUNCATE TABLE [dbo].[customer]
ROLLBACK TRANSACTION 
SELECT * FROM [dbo].[customer]

The ROLLBACK of the two transactions were successful. The deleted records were reloaded to the table after the ROLLBACK command as shown in the below image.

Wrap-up

The TRUNCATE command is in compliance with the ACID standard. The TRUNCATE and DELETE operations are logged in the transaction log. It only differs in what is getting logged, for example, the DELETE command will generate a log record for every affected row. The TRUNCATE command will deallocate the pages by marking it as free in GAM, IAM pages, and logs the deallocation information in the transaction log.

SQL Server uses transaction log for any roll back operations, when it comes to TRUNCATE it will still able to roll back the transaction as the TRUNCATE the operation is also getting logged.

The amount of log generated by the DELETE command is directly proportional to the number of rows it deletes. Due to which the DELETE command is considered to be slower than the TRUNCATE. There are many other differences between TRUNCATE and DELETE commands, for example

• The TRUNCATE command will fail when the underlying table is referenced by Foreign key constraint
• The TRUNCATE command will reset the IDENTITY value.
• The TRUNCATE command will not fire triggers. etc.,

With this demonstration, it is clear that the TRUNCATE statement can be rolled back in a transaction.