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

  • Hello Sarat,

    TRUNCATE TABLE is a DDL command and you need ALTER permissions for it, not just write permission like for a DELETE (=DML) command.

    It's identically to drop & re-create a table in just one command.


    Olaf Helper

    Blog Xing

    Thursday, November 08, 2012 10:31 AM
  • Hi,

    TRUNCATE is a DDL command.

    Check the below link for more details

    http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands


    PS.Shakeer Hussain

    Thursday, November 08, 2012 10:32 AM
  • TRUNCATE TABLE is listed in the DDL section of Books Online:

    TRUNCATE TABLE (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/3d544eed-3993-4055-983d-ea334f8c5c58

    Thursday, November 08, 2012 10:50 AM
  • have a look at this link

    http://msdn.microsoft.com/en-us/library/ff848799.aspx

    TRUNCATE is listed in the DDL command list, however the usage of TRUNCATE tempt  me to say that it is a DML :-)

    Regards
    Satheesh


    Thursday, November 08, 2012 11:05 AM
  •  

    The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

    As Deallocation of data pages occurs it is classified as DDL command for more information see below URL

    http://msdn.microsoft.com/en-us/library/ms177570.aspx


    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.

    Thursday, November 08, 2012 11:13 AM
  • The below link says that Truncate is a DML command.

    http://msdn.microsoft.com/en-us/library/ms175519(v=sql.105).aspx ( Refer to the schema Locks section).


    Thanks Sarat

    Friday, November 09, 2012 5:15 AM
  • The below link says that Truncate is a DML command.

    http://msdn.microsoft.com/en-us/library/ms175519(v=sql.105).aspx ( Refer to the schema Locks section).


    Thanks Sarat

    It is definitely a DML command as it is similar as Delete command. See this also

    Compared to the DELETE statement, TRUNCATE TABLE has the following advantages:

    • Less transaction log space is used.

      The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

    • Fewer locks are typically used.

      When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row.

    • Without exception, zero pages are left in the table.

      After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

    TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.


    Many Thanks & Best Regards, Hua Min



    Friday, November 09, 2012 5:20 AM
  • The below link says that Truncate is a DML command.

    http://msdn.microsoft.com/en-us/library/ms175519(v=sql.105).aspx ( Refer to the schema Locks section).


    Thanks Sarat

    This may be a documentation issue,That need to be intimated to Microsoft.

    Please refer the below link,It clearly shows truncate come under the DDL category

    http://msdn.microsoft.com/en-us/library/ff848799(v=sql.105).aspx


    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.

    Friday, November 09, 2012 5:27 AM
  • DDL command.
    Friday, November 09, 2012 5:51 AM
  • The below link says that Truncate is a DML command

    The document is just talking about "... such as table truncation ...", the TRUNCATE TABLE command isn't explicit mentioned. I agrre, this is more an issue of this document.

    6:2 for DDL ;-)


    Olaf Helper

    Blog Xing

    Friday, November 09, 2012 6:20 AM
  • That doco looks wrong. It's ddl. Why would a DML command need a super exclusive sch-m lock unless it was manipulating/defining(DDL) the underlying table
    Friday, November 09, 2012 6:39 AM
  • Cannot possibly be DDL if it is touching data.

    Since it is an alternative to <DELETE TableName>, it is a DML statement.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Friday, November 09, 2012 6:59 AM
    Moderator
  • Cannot possibly be DDL if it is touching data.


    DROP TABLE touches data as well.

    Olaf Helper

    Blog Xing

    Friday, November 09, 2012 7:27 AM
  • @Sarat,

    what is the pupose of your question? If this this DML (DDL) then ... what? The only purpose i see  - which chapter of BOL to look for the statement description .


    Serg

    Friday, November 09, 2012 7:45 AM
  • DROP TABLE touches data as well.


    In fact it does, due to quick-action DML & DDL combination implementation. It would safer though if it were only DDL and drop  only empty tables.

    But TRUNCATE TABLE does not do any DDL action, unless resetting IDENTITY seed considered a DDL action.

    BOL:"Removes all rows from a table without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources."


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Friday, November 09, 2012 10:37 AM
    Moderator
  • 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