Friday 13 August 2010

Truncate table DOES rollback

The post on table variables not being affected by transactions reminds me of another DBA interview question that I very rarely hear people answer correctly.

What is the difference between DELETE and TRUNCATE?

The standard answer trotted out is that DELETE is logged and can be rolled back, whereas TRUNCATE can't.

Really?

OK, try this:

CREATE TABLE #Trunc (MyId INT)
INSERT #Trunc SELECT 1 UNION SELECT 2


SELECT * FROM #Trunc 
-- returns 2 rows


BEGIN TRAN
  TRUNCATE TABLE #Trunc
  SELECT * FROM #Trunc 
-- returns 0 rows - we have truncated the table
ROLLBACK TRAN


SELECT * FROM #Trunc

Now if TRUNCATE was indeed non-logged and cannot be rolled back, you would expect the final SELECT statement to also return 0 records, and then laugh at you as you realised you had lost all your data ...

But it doesn't!  It returns the original 2 rows that were there before you issued the TRUNCATE statement ... if you don't believe me, give it a try ...

2 comments:

DBNoob said...

So then what is the difference between Delete and Truncate?

Grumpy DBA said...

Fair comment - I pointed out a common incorrect belief regarding TRUNCATE and DELETE without actually answering my own question!

Several differences between DELETE and TRUNCATE for getting rid of data from a table:
> DELETE can use a WHERE clause, while TRUNCATE affects the entire table
> DELETE can be run against a replicated table, TRUNCATE cannot
> only table owner and db_owner (and sysadmin) can run TRUNCATE whereas other users can use DELETE (if granted permission)
> DELETE is generally slower, as it writes a row to the transaction log for each row deleted from the table. TRUNCATE writes a page identifier to the log for each page from the table (page contains multiple rows of data), and simply drops the pages once the action is committed.

hope that helps

Post a Comment