Whenever I ask a SQL candidate in an interview the difference between DELETE and TRUNCATE TABLE, the first answer I get is
/*Open a Transaction And TRUNCATE TABLE*/
/* Check the Row Count*/
ROLLBACK
/* Check the Row Count*/
"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.
