Mladen Prajdić Blog

Blog about stuff and things and stuff. Mostly about SQL server and .Net

SQL Server: Why is TRUNCATE TABLE a DDL and not a DML operation and difference from DELETE

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

<span class="kwrd">truncate</span> <span class="kwrd">table</span> t1

<span class="kwrd">SELECT</span> resource_type, request_mode, resource_description, 
       resource_associated_entity_id
<span class="kwrd">FROM</span>   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.

 

kick it on DotNetKicks.com
 

Legacy Comments


Mladen
2007-10-04
re: SQL Server: Why is TRUNCATE TABLE a DDL and not a DML operation and difference from DELETE
thanx for reminding me. i'll add it to the list.

EdwinF
2007-10-05
re: SQL Server: Why is TRUNCATE TABLE a DDL and not a DML operation and difference from DELETE
Well, there is one thing that should be taken into account:
for instance, using the above example:

declare @a int
begin tran

truncate table t1

SELECT resource_type, request_mode, resource_description,
resource_associated_entity_id
FROM sys.dm_tran_locks

-- Adding these lines:
-- Asuming @passedparameter is from outside, and very big
SELECT @a = @PassedParameter
INSERT INTO t1 values (@a)
-- put more code here!!
-- end of addition

rollback

if the insertion fails (and actually, if any code after the truncate statement), when rolling back, THERE WILL BE NO DATA ON THE T1 TABLE because of the truncation, so, be afraid, very afraid!!!!

_____

Mladen
2007-10-05
re: SQL Server: Why is TRUNCATE TABLE a DDL and not a DML operation and difference from DELETE
could you please explain what do you mean by very big parameter?
for example:
create table t1 (id int)
insert into t1
select 1 union all
select 2 union all
select 3

-- we have data
select * from t1

begin tran
truncate table t1
-- this insert will fail
INSERT INTO t1 values ('gs')
rollback

-- we still have data
select * from t1

drop table t1

dharmendra kumar
2008-04-23
re: SQL Server: Why is TRUNCATE TABLE a DDL and not a DML operation and difference from DELETE
good post of discussion.

seema
2009-04-14
re: SQL Server: Why is TRUNCATE TABLE a DDL and not a DML operation and difference from DELETE
thnx..for the information,it helped me a lot.........rather added a n thing to my information!!!!!!!!!!!!!!

Bobby
2009-04-28
re: SQL Server: Why is TRUNCATE TABLE a DDL and not a DML operation and difference from DELETE
thanks for the info... very useful and neat

Torch
2010-06-16
re: SQL Server: Why is TRUNCATE TABLE a DDL and not a DML operation and difference from DELETE
Let's clarify something about Truncate and Delete that I recently pointed out in another site thread.

Truncate is a DDL, Truncate does not modify the data. Truncate never touches the data, thats a misconception. In every instance of SQL Truncate does the same thing, it simply marks the extents and lets them know the pages are to be reused (deallocated). The pages then (after commit) are ready for use by the system. This is also why you cannot use TRUNCATE with a "where" clause, your not deleting groups of data IN a table, your marking the data pages that make up that table to be reused by the server.

Delete is DML because it removes and logs each individual piece of data. Truncate is not truly part of CRUD, Create, Retrieve, Update, Delete, which is why its not truly DML and never will be. We're not really touching the data and doing things to it, we're touching the containers for the data, and metadata (schema).

The ability to rollback the Truncate is specific to the platform of SQL you are working with. Currently, only Microsoft SQL Server and Postgre (Post Ingres) log Truncate with begin and commit. Other platforms like Oracle log the DDL operations with "commit commit". Therefore there can be no rollback.

Some of you may ask, well if Truncate sets the pages to empty, how can you roll that back? Well like I said, in MS SQL Server and Postgre, BEGIN trans occurs in the log, so when a table is "flagged" it doesn't release the locks on the schema and the table until it's committed. Once committed, obviously, it can't be rolled back.

Paul White NZ
2010-10-12
re: SQL Server: Why is TRUNCATE TABLE a DDL and not a DML operation and difference from DELETE
Hey Mladen,

From Books Online (msdn.microsoft.com/en-us/library/ms175519.aspx):

"Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations."

That makes TRUNCATE TABLE an (optimized) DML operation (not DDL) doesn't it?

Paul