Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLTechnet

Vinoth is currently a Senior SQL Server DBA and 10 years of experience as SQL Server DBA. Started my career as a DBA in SQL 6.5/7 has worked in all subsequent SQL Server version. Vinoth has worked in some of the largest SQL server environments in the world in various domains ranging from Finance, Retail, Manufacturing, Consulting, Web etc. Vinoth has Engineering Degree in Computer Science and has certified in MCITP - Database Adminstrator in 2008/2005, MCDBA and ITIL Foundation V3.

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.

Comments

Leave a comment on the original post [www.sqltechnet.com, opens in a new window]

Loading comments...