Dec 232011
 

What are the differences between TRUNCATE and DELETE? You may immediately point out that:

  1. Truncates reset the identity value
  2. Truncates can’t have where clause
  3. Truncates can’t be run on a table which is referenced by other tables
  4. Truncates do not fire triggers.
  5. Truncates can’t be run on a table which has depending objects such as indexed views.
  6. Truncates can’t be run on a table which has published by merge or transactional replication publication
  7. Truncates need db_owner and db_ddladmin permission.
  8. Truncates need locks on the table and schema but do not need locks on rows of the tables


Internally, after a table is truncated, no data pages are left for the table whereas deletion over a heap may not release the database and deletion on a B-Tree may not release none leaf pages. Truncating command simply unhook data from metadata of the table. When table is small, unhooked data (rows) will be deallocated synchronously. When table is large, data will be removed asynchrnously which is called deferred deallocation.

Myth1: Truncate cannot be rolled back. That’s not true. Truncate Table command is transactional. It can be rolled back.
Myth2: Truncate generate less log records. It depends. If table is small enough, truncating table will generate more logs. See my experiment below

use master 
set nocount on
go
if DB_ID('test') is not null
drop database test
go
create database test 
go
use test
go
create table TestTable (id int identity(1,1) primary key, name varchar(1000) not null default(replicate('a', 1000)) )
go
insert into TestTable default values
go 
declare @maxlns varchar(100)
select @maxlns = MAX([Current LSN]) from fn_dblog(null, null)
delete TestTable
print 'After Deleting.........................................'
select [Current LSN], left(Operation, 16) as Operation, left(AllocUnitName, 30) as AllocUnitName, [Page ID],[Slot ID] from fn_dblog(null, null) where [Current LSN] > @maxlns
use master
if DB_ID('test') is not null
 drop database test
go
create database test 
go
use test
go
create table TestTable (id int identity(1,1) primary key, name varchar(1000) not null default(replicate('a', 1000)) )
go
insert into TestTable default values
go 1
declare @maxlns varchar(100)
select @maxlns = MAX([Current LSN]) from fn_dblog(null, null)
truncate table TestTable
print 'After Truncating.........................................'
select [Current LSN], left(Operation, 16) as Operation, left(AllocUnitName, 30) as AllocUnitName, [Page ID],[Slot ID] from fn_dblog(null, null) where [Current LSN] > @maxlns

Here is the result

After Deleting.........................................
Current LSN             Operation        AllocUnitName                  Page ID        Slot ID
----------------------- ---------------- ------------------------------ -------------- -----------
00000064:00000045:0001  LOP_BEGIN_XACT   NULL                           NULL           NULL
00000064:00000045:0002  LOP_DELETE_ROWS  dbo.TestTable.PK__TestTabl__32 0001:00000099  0
00000064:00000045:0003  LOP_SET_BITS     dbo.TestTable.PK__TestTabl__32 0001:00000001  0
00000064:00000045:0004  LOP_COMMIT_XACT  NULL                           NULL           NULL

After Truncating.........................................
Current LSN             Operation        AllocUnitName                  Page ID        Slot ID
----------------------- ---------------- ------------------------------ -------------- -----------
00000064:00000045:0001  LOP_BEGIN_XACT   NULL                           NULL           NULL
00000064:00000045:0002  LOP_LOCK_XACT    NULL                           NULL           NULL
00000064:00000045:0003  LOP_LOCK_XACT    NULL                           NULL           NULL
00000064:00000045:0004  LOP_MODIFY_ROW   sys.syscolpars.clst            0001:00000055  50
00000064:00000045:0005  LOP_MODIFY_ROW   dbo.TestTable.PK__TestTabl__32 0001:0000009a  0
00000064:00000045:0006  LOP_MODIFY_ROW   dbo.TestTable.PK__TestTabl__32 0001:00000001  0
00000064:00000045:0007  LOP_MODIFY_ROW   Unknown Alloc Unit             0001:00000001  0
00000064:00000045:0008  LOP_COUNT_DELTA  sys.sysallocunits.clust        0001:00000082  52
00000064:00000045:0009  LOP_COUNT_DELTA  sys.sysrowsets.clust           0001:00000012  91
00000064:00000045:000a  LOP_COUNT_DELTA  sys.sysrscols.clst             0001:00000033  102
00000064:00000045:000b  LOP_COUNT_DELTA  sys.sysrscols.clst             0001:00000033  103
00000064:00000045:000c  LOP_HOBT_DDL     NULL                           NULL           NULL
00000064:00000045:000d  LOP_MODIFY_ROW   sys.sysallocunits.clust        0001:00000082  52
00000064:00000045:000e  LOP_HOBT_DDL     NULL                           NULL           NULL
00000064:00000045:000f  LOP_MODIFY_ROW   sys.sysrowsets.clust           0001:00000012  91
00000064:00000045:0010  LOP_IDENT_SENTVA NULL                           NULL           NULL
00000064:00000045:0011  LOP_MODIFY_ROW   sys.syscolpars.clst            0001:00000055  50
00000064:00000045:0012  LOP_COMMIT_XACT  NULL                           NULL           NULL

So, the conclusion is Truncate Table generate more logs than Delete.
Myth3: Truncate is always faster. It depends. As our demo above, Truncate needs more operations than delete, thus theoretically it needs more time to process.

Deferred deallocation
While a large table is getting truncated, SQL Server merely unhooks the meta-data and IAMs then returns without resetting bits in IAM, PFS, GAM, and SGAM. An internal process will set those bits and then change the allocation related tables.

use master 
set nocount on
go
if DB_ID('test') is not null
 drop database test
go
create database test 
go
alter database test set recovery full
go
use test
go
create table TestTable (id int identity(1,1) primary key, name varchar(1000) not null default(replicate('a', 1000)) )
go
insert into TestTable default values
go 10000
declare @maxlns varchar(100)
select @maxlns = MAX([Current LSN]) from fn_dblog(null, null)
print 'Before Truncating/Deleting.........................................'
select 
 CONVERT (INT, SUBSTRING (sa.first_iam_page, 6, 1) + SUBSTRING (sa.first_iam_page, 5, 1)) as [first_iam_file],
 CONVERT (INT, SUBSTRING (sa.first_iam_page, 4, 1) + SUBSTRING (sa.first_iam_page, 3, 1) + SUBSTRING (sa.first_iam_page, 2, 1) + SUBSTRING (sa.first_iam_page, 1, 1)) as [first_iam_page],
 CONVERT (INT, SUBSTRING (sa.root_page, 6, 1) + SUBSTRING (sa.root_page, 5, 1)) as [root_page_file],
 CONVERT (INT, SUBSTRING (sa.root_page, 4, 1) + SUBSTRING (sa.root_page, 3, 1) + SUBSTRING (sa.root_page, 2, 1) + SUBSTRING (sa.root_page, 1, 1)) as [root_page],
 CONVERT (INT, SUBSTRING (sa.first_page, 6, 1) + SUBSTRING (sa.first_page, 5, 1)) as [first_page_file],
 CONVERT (INT, SUBSTRING (sa.first_page, 4, 1) + SUBSTRING (sa.first_page, 3, 1) + SUBSTRING (sa.first_page, 2, 1) + SUBSTRING (sa.first_page, 1, 1)) as [first_page],
   type_desc
 from sys.system_internals_allocation_units sa
  inner join sys.partitions sp on sa.container_id = sp.partition_id
where object_name(sp.object_id) like 'TestTable%'
truncate table TestTable
--delete TestTable
print 'After Truncating/Deleting.........................................'
select 
 CONVERT (INT, SUBSTRING (sa.first_iam_page, 6, 1) + SUBSTRING (sa.first_iam_page, 5, 1)) as [first_iam_file],
 CONVERT (INT, SUBSTRING (sa.first_iam_page, 4, 1) + SUBSTRING (sa.first_iam_page, 3, 1) + SUBSTRING (sa.first_iam_page, 2, 1) + SUBSTRING (sa.first_iam_page, 1, 1)) as [first_iam_page],
 CONVERT (INT, SUBSTRING (sa.root_page, 6, 1) + SUBSTRING (sa.root_page, 5, 1)) as [root_page_file],
 CONVERT (INT, SUBSTRING (sa.root_page, 4, 1) + SUBSTRING (sa.root_page, 3, 1) + SUBSTRING (sa.root_page, 2, 1) + SUBSTRING (sa.root_page, 1, 1)) as [root_page],
 CONVERT (INT, SUBSTRING (sa.first_page, 6, 1) + SUBSTRING (sa.first_page, 5, 1)) as [first_page_file],
 CONVERT (INT, SUBSTRING (sa.first_page, 4, 1) + SUBSTRING (sa.first_page, 3, 1) + SUBSTRING (sa.first_page, 2, 1) + SUBSTRING (sa.first_page, 1, 1)) as [first_page],
   type_desc--,*
 from sys.system_internals_allocation_units sa
  inner join sys.partitions sp on sa.container_id = sp.partition_id
where object_name(sp.object_id) like 'TestTable%'
print 'right after truncate...................................'
select [Current LSN], left(Operation, 16) as Operation, left(AllocUnitName, 30) as AllocUnitName, [Page ID],[Slot ID] from fn_dblog(null, null) where [Current LSN] > @maxlns
--select [Current LSN], left(Operation, 16) as Operation, left(AllocUnitName, 30) as AllocUnitName, [Page ID],[Slot ID] from fn_dblog(null, null) where [Current LSN] > @maxlns and AllocUnitName like 'dbo.TestTable%'
select @maxlns = MAX([Current LSN]) from fn_dblog(null, null)
waitfor delay '00:02:00'
print '2 minutes after truncate...................................'
select [Current LSN], left(Operation, 16) as Operation, left(AllocUnitName, 30) as AllocUnitName, [Page ID],[Slot ID] from fn_dblog(null, null) where [Current LSN] > @maxlns

Results are

Beginning execution loop
Batch execution completed 10000 times.
Before Truncating/Deleting.........................................
first_iam_file first_iam_page root_page_file root_page   first_page_file first_page  type_desc
-------------- -------------- -------------- ----------- --------------- ----------- ------------------------------------------------------------
1              154            1              791         1               153         IN_ROW_DATA

After Truncating/Deleting.........................................
first_iam_file first_iam_page root_page_file root_page   first_page_file first_page  type_desc
-------------- -------------- -------------- ----------- --------------- ----------- ------------------------------------------------------------
0              0              0              0           0               0           IN_ROW_DATA

right after truncate...................................
Current LSN             Operation        AllocUnitName                  Page ID        Slot ID
----------------------- ---------------- ------------------------------ -------------- -----------
0000009e:000000fc:0001  LOP_BEGIN_XACT   NULL                           NULL           NULL
0000009e:000000fc:0002  LOP_LOCK_XACT    NULL                           NULL           NULL
0000009e:000000fc:0003  LOP_LOCK_XACT    NULL                           NULL           NULL
0000009e:000000fc:0004  LOP_MODIFY_ROW   sys.syscolpars.clst            0001:00000055  50
0000009e:000000fc:0005  LOP_LOCK_XACT    NULL                           NULL           NULL
0000009e:000000fc:0006  LOP_COUNT_DELTA  sys.sysallocunits.clust        0001:00000082  52
0000009e:000000fc:0007  LOP_COUNT_DELTA  sys.sysrowsets.clust           0001:00000012  91
0000009e:000000fc:0008  LOP_COUNT_DELTA  sys.sysrscols.clst             0001:00000033  102
0000009e:000000fc:0009  LOP_COUNT_DELTA  sys.sysrscols.clst             0001:00000033  103
0000009e:000000fc:000a  LOP_HOBT_DDL     NULL                           NULL           NULL
0000009e:000000fc:000b  LOP_MODIFY_ROW   sys.sysallocunits.clust        0001:00000082  52
0000009e:000000fc:000c  LOP_DELETE_ROWS  sys.sysallocunits.nc           0001:00000035  103
0000009e:000000fc:000d  LOP_MODIFY_HEADE Unknown Alloc Unit             0001:00000001  0
0000009e:000000fc:000e  LOP_SET_BITS     sys.sysallocunits.nc           0001:00000001  0
0000009e:000000fc:000f  LOP_INSERT_ROWS  sys.sysallocunits.nc           0001:00000035  0
0000009e:000000fc:0010  LOP_MODIFY_ROW   sys.sysallocunits.clust        0001:00000082  52
0000009e:000000fc:0011  LOP_MODIFY_ROW   Unknown Alloc Unit             0001:00000009  0
0000009e:000000fc:0012  LOP_INSERT_ROWS  sys.sysallocunits.clust        0001:00000082  53
0000009e:000000fc:0013  LOP_INSERT_ROWS  sys.sysallocunits.nc           0001:00000035  105
0000009e:000000fc:0014  LOP_HOBT_DDL     NULL                           NULL           NULL
0000009e:000000fc:0015  LOP_MODIFY_ROW   sys.sysrowsets.clust           0001:00000012  91
0000009e:000000fc:0016  LOP_IDENT_SENTVA NULL                           NULL           NULL
0000009e:000000fc:0017  LOP_MODIFY_ROW   sys.syscolpars.clst            0001:00000055  50
0000009e:000000fc:0018  LOP_COMMIT_XACT  NULL                           NULL           NULL

2 minutes after truncate...................................
Current LSN             Operation        AllocUnitName                  Page ID        Slot ID
----------------------- ---------------- ------------------------------ -------------- -----------
0000009e:00000102:0001  LOP_BEGIN_XACT   NULL                           NULL           NULL
0000009e:00000102:0002  LOP_LOCK_XACT    NULL                           NULL           NULL
0000009e:00000102:0003  LOP_MODIFY_ROW   Unknown Alloc Unit             0001:0000009a  0
0000009e:00000102:0004  LOP_MODIFY_ROW   Unknown Alloc Unit             0001:00000001  0
0000009e:00000102:0005  LOP_SET_BITS     Unknown Alloc Unit             0001:00000003  1
0000009e:00000102:0006  LOP_MODIFY_ROW   Unknown Alloc Unit             0001:0000009a  0
...
0000009e:00000102:0012  LOP_MODIFY_ROW   Unknown Alloc Unit             0001:0000009a  0
0000009e:00000102:0013  LOP_MODIFY_ROW   Unknown Alloc Unit             0001:00000001  0
0000009e:00000102:0014  LOP_SET_BITS     Unknown Alloc Unit             0001:00000003  1
0000009e:00000102:0015  LOP_SET_BITS     Unknown Alloc Unit             0001:00000002  1
...
0000009e:00000102:022e  LOP_SET_BITS     Unknown Alloc Unit             0001:00000002  1
0000009e:00000102:022f  LOP_LOCK_XACT    NULL                           NULL           NULL
0000009e:00000102:0230  LOP_MODIFY_ROW   Unknown Alloc Unit             0001:00000001  0
0000009e:00000102:0231  LOP_DELETE_ROWS  sys.sysallocunits.nc           0001:00000035  0
0000009e:00000102:0232  LOP_DELETE_ROWS  sys.sysallocunits.clust        0001:00000082  52
0000009e:00000102:0233  LOP_SET_BITS     sys.sysallocunits.clust        0001:00000001  0
0000009e:00000102:0234  LOP_COMMIT_XACT  NULL                           NULL           NULL
0000009e:0000015b:0001  LOP_EXPUNGE_ROWS sys.sysallocunits.nc           0001:00000035  0
0000009e:0000015b:0002  LOP_EXPUNGE_ROWS sys.sysallocunits.nc           0001:00000035  103
0000009e:0000015b:0003  LOP_SET_BITS     sys.sysallocunits.nc           0001:00000001  0
0000009e:0000015b:0004  LOP_EXPUNGE_ROWS sys.sysallocunits.clust        0001:00000082  52
0000009e:0000015b:0005  LOP_SET_BITS     sys.sysallocunits.clust        0001:00000001  0
0000009e:0000015b:0006  LOP_MODIFY_HEADE Unknown Alloc Unit             0001:00000001  0

From result we can see that

  1. Line 6: indicate that the first IAM page is page 154, which is 0x9A in hexdecimal.
  2. Line 51: modify IAM page.
  3. Line 52: modify PFS page.
  4. Line 53: modify SGAM page.
  5. Line 54: modify GAM page.
  6. Line 59 and after: release the IAM chain from the system.

  One Response to “Truncate”

  1. Great Article.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.