Remember the Bill Gates email hoax? The one that claims to be some sort of Microsoft beta test saying Gates is going to pay cash money to anyone who forwards the email? I’m pretty sure it’s been around as long as actual email.

You knew that wasn’t real, right? It’s a myth. And speaking of perpetuated myths…

Lately I’ve had the pleasure of participating in interviews for an open position on our DBA team, which as I’ve noted in previous posts I certainly enjoy. During these interviews it’s not uncommon for someone on our team to ask prospective employees to describe the differences between DELETE and TRUNCATE TABLE.

We’re hoping for an answer that includes use cases or discusses records and pages.

What we often get is, almost verbatim among interviewees, “DELETE is logged, TRUNCATE is not.”

That is…not true. Insert sad trombone. In fact interviewees often double down on this myth by adding something like “So you can’t roll back from TRUNCATE TABLE.”

If there were a current list of persistent SQL Server myths this would definitely be on it. In fact, Paul Randal published just such a list way back in 2010 in an attempt to clear up the confusion. Here’s what Randal said about TRUNCATE TABLE:

There is no such thing as a non-logged operation in a user database…A TRUNCATE TABLE operation does a wholesale delete of all data in the table. The individual records are not deleted one-by-one, instead the data pages comprising the table are simply deallocated. The allocations are unhooked from the table and put onto a queue to be deallocated by a background task called the deferred-drop task.

Deallocated is a nice way of saying they are marked for death, like the guy in the bathtub full of ice with a note about his kidney being removed. That’s also a myth. The guy in the tub, that is – not the part about deallocated pages.

Randal even gives an example in his post where he shows the log records for a TRUNCATE TABLE. And if it’s logged then it can be rolled back.

In case you think I’m making this up like some hypothetical Nigerian Prince, just try this script with a TRUNCATE TABLE that gets rolled back.

USE tempdb;
GO
 
-- I don't usually 'SELECT *' but...
SELECT *
INTO SomeTable
FROM sysobjects;
 
-- How many records we got?
SELECT COUNT(*)
FROM SomeTable;
 
BEGIN TRAN
 
-- Let's make all records disappear.
TRUNCATE TABLE SomeTable;
 
SELECT COUNT(*) FROM SomeTable;
 
-- They said this couldn't be done.
-- Oh yeah? Hold my beer.
ROLLBACK
 
-- C’est impossible!
SELECT COUNT(*) FROM SomeTable;

No one is born knowing this stuff, but that doesn’t mean somewhere you may have acquired bad knowledge. I hope this post helps you with any upcoming interviews, or at least with your junk email.