Truncate table command in SQL server is a efficiently logged operation

17 Dec

There is common misconception between developers that truncate table command is a non logged operation or minimally logged operation. Actually it is not. It is a fully logged but also efficiently logged operation. Those who are not familiar with these terms here is a quick explanation.
In SQL server, minimally logged operation is a database operation that only generates log records for data page level allocations but not for all row level changes for the selected pages. i.e. Rebuilding a index would generate log records for page level allocations in the transactions log but not row level changes. whereas in fully logged operation it will record all row level changes in addition to page level changes(happens with delete command) in after images and before images in transaction log records. When you run Truncate table command it will deallocate all the pages associated with the selected table. Pages and the extents are deallocated which are associated with the table. But still operation is logged in transaction log only the data pages deallocation happen instantly,so it might be faster at some situations. Truncate table command is a efficiently logged operation. However if it is a large table there might be a need to get large amount of locks for the pages and hence it might be slower if you are using SQL server 2000 SP2 or earlier. This has been fixed with SQL Server 2000 SP3 and upper with deferred drop mechanism where truncate table command runs as separate steps. this solves the issue of running out of extent locks. You can experiment with the deallocations as following scripts.

--Create test table 
      Id INT IDENTITY(1, 1) ,
      [Name] CHAR(1000) DEFAULT 'test value'

-- insert 1000 test records

INSERT into dbo.test


GO 1000

-- Clear the Transaction log so wee can see later easily

-- Run truncate table 


-- See the transaction log records



Leave a comment

Posted by on December 17, 2012 in ASP.NET, MS SQL Server, SQL, TSQL


Tags: ,

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: