Truncate Vs Delete in SQL Server

If you have been looking for job in Database related field then you might be already familiar with this age old question (which I was asked in almost all interviews) is “What is difference between DELETE and TRUNCATE ?” And usually if you read on internet you will see that people answer it with just one line that,

When you use DELETE to delete records, information is stored in transaction log and hence it can be rolled back. And when you use TRUNCATE, no information is stored in log and so you can’t it rollback.

But technically it is wrong. I mean it is true that when you use TRUNCATE it doesn’t gets logged into transaction log, but it simply wrong to say that truncate will not let you rollback your operation. Code below can explain this operation,

-- Select some table 
select * from tableBar

-- Create transaction

-- Truncate table
Truncate table tableBar

-- Rollback transaction

-- Voila!! You will still have your data as it is
select * from tableBar

So basically you can “hack” the system to let you rollback TRUNCATE operation.

Which means if you do these operations without conjunction of any other statements then the answer stands true. But if you are using these operations with mix of other statements (like one in code above) results can be manipulated Devil. So in the end true answer is “it depends” (Open-mouthed smile, I think it is most common answer you will hear from any database professional).

Also, when you use TRUNCATE, Database engine actually de-allocates physical pages related to that table and hence you are not able to rollback any TRUNCATE operation. Another difference is you can do selective DELETE (based on some selection criteria) but can only TRUNATE whole table.

That’s it for now.

It’s Just A ThoughtFingers crossed

Gaurang Sign

Leave a Reply

Your email address will not be published. Required fields are marked *