First let's look at the difference assuming we want to delete the whole table like truncate does.
Truncate:
- deallocates the data pages in a table and only this deallocation is stored in transaction log
- aquires only table and page locks for the whole table. since no row locks are used less memory is required (lock is a pure memory object)
- resets identity column if there is one
- removes ALL pages. NO empty pages are left behind in a table
- fast(er)
- doesn't fire delete triggers
Delete:
- removes one row at the time and every deleted row is stored in the transaction log
- aquires table and/or page and row locks for the whole table
- leaves identity column alone
- can leave empty pages in a table since empty page removal requires a table lock which doesn't necessarily happen
- slow(er)
- fires delete triggers
What this means that a Truncate can also be rolled back much like a delete as is evident with this code:
create table t1 (id int)
insert into t1
select 1 union all
select 2 union all
select 3
select * from t1
begin transaction
truncate table t1
rollback
select * from t1
drop table t1
So why is it a DDL and not a DML statement? After all we are modifying the data, aren't we?
Well yes... but let's take a deeper look:
create table t1 (id int)
insert into t1
select 1 union all
select 2 union all
select 3
begin tran
truncate table t1
SELECT resource_type, request_mode, resource_description,
resource_associated_entity_id
FROM sys.dm_tran_locks
rollback
drop table t1
We can see that the Truncate has taken a Sch-M (schema modification) lock. This probably comes from the identity reset since we're
modifying a table property which counts as schema. (this is my observation on behaviour and isn't necessarily correct)
One other important aspect of this Sch-M lock is that while Truncate is in progress you can't modify a table in any way.
You can't update or insert into a table from another transaction contrary to when deleting data when you can still update or insert from another transaction.
