I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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 

    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.

 

kick it on DotNetKicks.com
 

Print | posted on Wednesday, October 03, 2007 10:20 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# 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.
10/4/2007 10:16 AM | Mladen
Gravatar

# 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!!!!

_____
10/5/2007 9:12 PM | EdwinF
Gravatar

# 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
10/5/2007 9:22 PM | Mladen
Gravatar

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

good post of discussion.
4/23/2008 12:51 PM | dharmendra kumar
Gravatar

# 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!!!!!!!!!!!!!!
4/14/2009 5:02 PM | seema
Gravatar

# 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
4/28/2009 9:19 PM | Bobby
Gravatar

# 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.
6/16/2010 5:53 PM | Torch
Gravatar

# 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
10/12/2010 3:27 PM | Paul White NZ
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET