Truncate transaction rollback operation method in SQL Server _mssql2005

We generally think that truncate is an operation that cannot be rolled back, it deletes all the data in the table and resets the identity column.

If you do a truncate operation in a transaction, you can roll back. Conversely, it does not recover data from the log file file. It does not record the deleted data in the log file, it only records the cell allocation of the data page in the log.

The following example will explain what is said above.

Copy Code code as follows:

Use Temp_test_database
Go
--Create a temporary table
CREATE TABLE Truncatetabel (ID INT)
INSERT into Truncatetabel (ID)
SELECT 1
UNION All
SELECT 2
UNION All
SELECT 3
Go

Copy Code code as follows:

--Check the inserted data
SELECT * from Truncatetabel

As shown in figure:

Start execution of a transaction

Copy Code code as follows:

--Start a transaction
BEGIN TRAN
TRUNCATE TABLE Truncatetabel
Go
--check truncatetable before rolling back
SELECT * from Truncatetabel
Go

F5 execution, as shown in figure:

To perform a ROLLBACK transaction

Copy Code code as follows:

--Rolling back transactions
ROLLBACK TRAN
Go

Check the table again truncatetable

Copy Code code as follows:

--check truncatetable again after rolling back
SELECT * from Truncatetabel
Go

F5 execution, as shown in figure:

To sum up, a transaction can be rolled back to a truncate operation.