What are the differences between TRUNCATE and DELETE? You may immediately point out that:
- Truncates reset the identity value
- Truncates can’t have where clause
- Truncates can’t be run on a table which is referenced by other tables
- Truncates do not fire triggers.
- Truncates can’t be run on a table which has depending objects such as indexed views.
- Truncates can’t be run on a table which has published by merge or transactional replication publication
- Truncates need db_owner and db_ddladmin permission.
- 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
- Line 6: indicate that the first IAM page is page 154, which is 0x9A in hexdecimal.
- Line 51: modify IAM page.
- Line 52: modify PFS page.
- Line 53: modify SGAM page.
- Line 54: modify GAM page.
- Line 59 and after: release the IAM chain from the system.