Rollback the TRUNCATE Command in SSMS

We know that TRUNCATE is DDL command. So, by properties, it is auto commit.

Some people says DDL operation is not logged in log file. But, in my opinion when we run TRUNCATE in SQL Server then it track the deallocated pages in log file.

If we use TRUNCATE in a truncation scope, then we can roll back the data or table.

Now will see this concept practically.

Let’s create a table called “Sample_table”

CREATE TABLE Sample_table (ID INT,name varchar(30))

INSERT INTO Sample_table values (1,'Pritam')

go

INSERT INTO Sample_table values (2,'Lisa')

go

INSERT INTO Sample_table values (3,'Sourav')

We will check the table data.

SELECT * FROM Sample_table

Output:

Now will go for Transection scope.

BEGIN TRAN

TRUNCATE TABLE Sample_table

After truncate the table, will check the table value.

SELECT * FROM Sample_table

Output:

Now, will do roll back.

ROLLBACK TRAN

After roll back  the table, will check the table value.

SELECT * FROM Sample_table

Output: