September 5, 2009

SQL SERVER: Difference between DELETE and TRUNCATE commands

We interviewed many people my company as recruitments are going on for developers. When I asked this SQL SERVER question to person "What is difference between DELETE and TRUNCATE in SQL SERVER?".

I got the following  answers from most of them are, which are incorrect:

1. I can not use WHERE condition with TRUNCATE command
2. I can not use TRUNCATE command if foreign key is there on table.
3. TRUNCATE is faster than the DELETE, as  DELETE write records them in Log file in case it is needed to rollback in future from LOG files. etc..

These answers are correct. I also got this answer, which is Incorrect:

"DELETE can be rolled back while TRUNCATE can not be rolled back"

I asked them what does it mean?, give me an example. See what they say, if I have Transaction and if I have used DELETE then ROLLBACK will let them back to original state. In case of TRUNCATE within Transaction, will not allow me to original state.

This is incorrect. We can ROLLBACK changes made by DELETE and TRUNCATE if the it is used in Transaction. Lets see this in detail by example:

Create one table table and insert some data in it like:
CREATE Test TABLE(
ID INT IDENTITY
Text VARCHAR(5)
)
GO
INSERT INTO Test(Text) VALUES('A')
INSERT INTO Test(Text) VALUES('B')
INSERT INTO Test(Text) VALUES('C')
INSERT INTO Test(Text) VALUES('D')
GO

Lets try to use TRUNCATE within Transaction as follows:
BEGIN TRAN --Start Transaction
TRUNCATE TABLE Test --Use this command
--Check the data from table. See transaction is still in progress not commit/Rollback yet
--There is no row in table, as all rows were deleted from above TRUNCATE statement
SELECT * FROM Test 
ROLLBACK --Rollback this Transaction
--Lets see now what values should be there in table after ROLLBACK
SELECT * FROM Test
So, we found that we can found all values in the table. We can ROLLBACK the TRUNCATE command as same as other commands.
This is place where most of the developers can not give accurate answer. I hope by this article they can have clear idea.

Let me know if there is any confusion.

CONCLUSION:

DELETE and TRUNCATE both can be rolled back when used with TRANSACTION.
If Transaction is done, means COMMITED, then we can not rollback TRUNCATE command, but we can still rollback DELETE command from LOG files, as  DELETE write records them in Log file in case it is needed to rollback in future from LOG files.

Reference: Tejas Shah( http://www.SQLYoga.com )

1 comment:

  1. hi tejas
    thank u for the above information
    can u plz upload the correct answer

    ReplyDelete