SQL Tips by Namwar Rizvi

August 17, 2007

TRUNCATE command can be Rolledback

Filed under: Information,Performance,TSQL — namwar @ 4:05 PM

Some people wonder why TRUNCATE is so faster than a DELETE operation? Most of the time answer is DELETE is a logged operation and TRUNCATE is not a logged operation therefore, TRUNCATE is faster than DELETE.
This undertsanding is not fully correct. TRUNCATE is also a logged operation and it will also be rolled back if you roll back your transaction exactly as the rollback of DELETE operation.
But there is a difference in the logging of DELETE and TRUNCATE. A DELETE operation is logged on per row basis therefore, the time to delete full table is nearly directly proportional to the number of rows it has. On the other hand, in a TRUNCATE operation SQL Server logs all data pages which get deallocated because of the TRUNCATE command. You can take the analogy of File system here. Your operating system does not take more time to delete a large file than to a smaller file. This is because for both type of files, OS needs to do the same set of operations which is atcually deallocating the sectors used by the target file and this can be achieved very quickly regardless of the size of the file. Same logic applies in TRUNCATE command also the only difference is since RDBMS guarntees you ACID properties therefore, it provides you Rollback facility of the TRUNCATE if you performed it in a Transaction.
Following example will help you to actually see it in action:

Use tempdb
Go

–Create a test table
Create table testTable (id int)

–Insert three sample rows
Insert into testTable values (1)
Insert into testTable values (2)
Insert into testTable values (3)

–Display all rows of the table
Select * from testTable

–Perform transactional truncate
Begin Transaction
Truncate table testTable

Rollback transaction

–Verify that rows are still there
Select * from testTable

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: