Can we rollback the Truncate, Drop, and Delete command deleted data?

Here we talk about the concept of truncate, drop and delete commands and whether they can be rollback, if so, how and when !!

Yesterday I was working on this and see if we use the truncate command in transaction, we can rollback the data. One more thing if we perform delete command outside the transaction, we can’t rollback the data.

Let’s try this…

Suppose I have a table dbo.Employee with three column- Id, Name, and Salary.

CREATE TABLE [dbo].Employee(
      Id [int] NULL,
      Name [varchar](50) NULL,
      Salary [Int] NULL
)

Insert the values into the db.Employee table

INSERT INTO dbo.Employee VALUES(1,'Employee1',10000)
INSERT INTO dbo.Employee VALUES (2,'Employee2',20000)
INSERT INTO dbo.Employee VALUES (3,'Employee3',30000)
INSERT INTO dbo.Employee VALUES (4,'Employee4',10000)
INSERT INTO dbo.Employee VALUES (5,'Employee5',30000)

Now I have a five rows in table

SELECT * FROM dbo.Employee

Now we perform the delete command in Transaction and then select command

BEGIN TRAN
DELETE FROM dbo.Employee WHERE ID = 2
ROLLBACK

SELECT * FROM dbo.Employee

Output:

But again I run the select command then I get the same result as previous select statement (i.e 5).
Now I perform the Truncate command in Transaction, Let see what happen.

BEGIN TRAN
TRUNCATE TABLE dbo.Employee
ROLLBACK

Now again I run the select command and got the same rows and same result, if same thing we do with Drop command that will also give same result.
It means If we use the Delete, Truncate, and Drop command with Transaction, we can rollback the data and if we perform all three command outside the Transaction we can’t rollback.

One more thing I have to add in this article, we can’t rollback the data but we can restore the data from the Transaction_log, if we use the delete command. We can’t restore the data if we use the truncate or drop command. How and Why read in next article.

In summary, 
We can rollback the DDL and DML commands, if all these commands are in transaction. We can only restore the DML commands data.