Friday 13 September 2013

Truncate can/can't be roll backed!!!!!!!!!!!!!

One major difference(its partially myth) between truncate and delete is that truncate can't be rolled back while delete can be rolled back.
But its  a myth .Truncate can also be rolled back but only if it is in Transaction statement.I will try to explain with some example .

 Lets a table users.
Case 1:
BEGIN TRAN
  TRUNCATE TABLE users    
  ROLLBACK
END TRAN

Now execute -
SELECT * FROM Users ; will return all data .

Obviously DELETE will return same result means all data will be returned.

Case 2:

Now execute 

TRUNCATE TABLE users    
and then execute immediately 
ROLLBACK  (here rollback without begin transaction)
Then execute 
SELECT * FROM Users ; NO DATA will be returned,means here table is not rolled back

But DELETE can be rolled back in this situation

Now execute 

DELETE TABLE users    
and then execute immediately 
ROLLBACK  (here rollback without begin transaction)
Then execute 
SELECT * FROM Users ;  All data will be returned i.e. table rolled back successfully.

Conclusion:

TRUNCATE can be rolled back when used with in TRANSACTION,while DELETE can be rolled back either used within transaction or outside transaction block.