Rollback Table after Truncate Command in Sql Server

By -

It is misconception among people that Truncate cannot be rolled back. But in reality Truncate operation can be Rolled Backed before Commit.Here is the Sql query

-- Create table 
CREATE TABLE Employee
                  ( EmpID INT PRIMARY KEY,
                    EmpSalary INT
                  )

 -- Check data before Truncate
SELECT * FROM Employee







--Begin Transaction
BEGIN TRAN
--Truncate Table
TRUNCATE TABLE Employee
GO
--Check data after Truncate
SELECT * FROM Employee
GO
--Rollback Transaction
ROLLBACK TRAN
GO
--Check the data after Rollback
SELECT * FROM Employee
GO







Note:- If Transaction is done, mean Committed then we can not rollback Truncate command from log file but we can restore data using MSSQL Server Management Studio.

 Check for difference between Delete and Truncate in next article.

Give your valuable comments it's encourage me to publish new articles.

Comments

Post a Comment

Popular posts from this blog

Enable HTTP Transport Security (HSTS) in IIS 7

By -
Q.  What is the best way to turn on   HTTP Strict Transport Security   on an IIS 7 web server? Ans:  This can be done by adding following block in Web.Config:               <system.webServer>                     <httpProtocol>                          <customHeaders>                               <add name ="X-CustomName" value="MyCustomValue"/>                                               </customHeaders>                    </httpProtocol>             </system.webServer> We have to configure ...
Read more

Steps To Create New Project/Team On TFS

By -
Image
Select New item from drop-down tab that will be on left of Dashboard tab. On click New Items from drop-down will open Create new team pop-up:-    Put new Team name > Give Description > Select permissions (if permissions is already there else create a new permissions). Create a new sprint:- Click on iteration > New > Add Iteration name > Start Date & End Date > Click Save and close button. Go to newly created project/team dashboard. Click Backlog in Work area Newly created sprint will be show in Backlog items:-
Read more

Difference Between Delete & Truncate in Sql Server

By -
INTRODUCTION While working on database, we are using Delete and Truncate without knowing the differences between them and when to use them . In this article we will discuss the difference between Delete and Truncate in Sql. Delete Delete is a DML command. Delete statement is executed using a row lock,each row in the table is locked for deletion. We can specify filters in where clause. It deletes specified data if where condition exists. Delete activities a trigger because the operation are logged individually. Slower than Truncate because it Keeps logs Truncate Truncate is a DDL command. Truncate table always lock the table and page but not each row.As it removes all the data. Cannot use Where condition.  It Removes all the data. Truncate table cannot activate a trigger because the operation does not log individual row deletions. Faster in performance wise, because it doesn't keep any logs. Note Delete and Truncate both can be rolled back when used with Tr...
Read more