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 3select * 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 3begin 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.
|
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:
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 |