Monday, 6 August 2012

Rollback TRUNCATE TABLE statement

Whenever I ask a SQL candidate in an interview the difference between DELETE and TRUNCATE TABLE, the first answer I get is

"DELETE is a logged operation and TRUNCATE is a NON-Logged Operation"

Is TRUNCATE really a Non-Logged Operation?

BOL refers to TRUNCATE operations as “minimally logged” operations,
So what really happens during TRUNCATE TABLE statement?

TRUNCATE Operation does not remove data instead it deallocates whole data pages and removes pointers to indexes. Hence there is a minimal Log entry for TRUNCATE operation and can be rolled back completely.

Let us try with an exercise, 

/* Creating a Table and Populating the numbers Table*/
CREATE TABLE Numbers(n int not null primary key);
GO
INSERT into numbers(n)
SELECT rn from (select row_number() OVER(order by current_timestamp) as rn
from sys.trace_event_bindings as b1
, sys.trace_event_bindings as b2) as rd
where rn < 5000
GO

/* Check the Row Count*/
SELECT count(*) from numbers
-----------
4999
(1 row(s) affected)

/*Open a Transaction And TRUNCATE TABLE*/
BEGIN TRAN
TRUNCATE TABLE numbers

/* Check the Row Count*/
SELECT count(*) from numbers
-----------
0
(1 row(s) affected)

ROLLBACK

/* Check the Row Count*/
SELECT count(*) from numbers
-----------
4999
(1 row(s) affected)

from the above example i was able to rollback a TRUNCATE TABLE Statement and was able to retrieve all the 4999 rows.

1 comment:

  1. It is true in most cases, however DELETE and TRUNCATE can both be rolled back when they are executed inside a ‘BEGIN TRANSACTION’ block and the current session has not yet ended.
    In other words, when the TRUNCATE statement has not been committed yet, it can be rolled back

    ReplyDelete