none
Is Truncate a DDL command or DML command?

Answers

  • 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.

     Ref :http://weblogs.sqlteam.com/mladenp/archive/2007/10/03/SQL-Server-Why-is-TRUNCATE-TABLE-a-DDL-and-not.aspx


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Friday, November 09, 2012 12:11 PM

All replies