SQL Server's TRUNCATE transaction rollback operation method, servertruncate

SQL Server's TRUNCATE transaction rollback operation method, servertruncate

We generally think that TRUNCATE is an operation that cannot be rolled back. It will delete all data in the table and reset the Identity column.

If you perform the TRUNCATE operation in the transaction, you can roll back. Otherwise, it will not recover data from the log file. It does not record the deleted data in the log file. It only records the unit allocation on the data page in the log.

The following example explains the above.

Copy codeThe Code is 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 codeThe Code is as follows: -- check the inserted data
SELECT * FROM TruncateTabel

Start transaction execution

Copy codeThe Code is as follows:
-- Start transaction
BEGIN TRAN
Truncate table TruncateTabel
GO
-- Check TruncateTable before rollback
SELECT * FROM TruncateTabel
GO

F5 execution,

Execute rollback transactions

Copy codeThe Code is as follows: -- roll back the transaction
ROLLBACK TRAN
GO

Check TruncateTable again

Copy codeThe Code is as follows: -- check TruncateTable again after rollback
SELECT * FROM TruncateTabel
GO

F5 execution,

To sum up, transactions can roll back the TRUNCATE operation.