Delete v Truncate

  • Lokesh Vij

    SSChampion

    Points: 10836

    Stuart Davies (3/21/2013)


    Thanks for the question - good reminder of the basics.

    It appears that quite a few here needed reminding of the differences - 40% wrong at the moment.

    I wonder how much higher it would have been if you asked which are logged in the question :w00t:

    +1

    well said Stuart..

    What about which can be Rollbacked 🙂

    Lot of ppl have confusion over this as well 😀

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • nenad-zivkovic

    Default port

    Points: 1446

    Is there an official list of DDL operations or is its DDL-ness decided by community consensus? 🙂

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

    Also, it can be checked by user rights. Members of db_datawriter role can run DELETE command, but can not TRUNCATE. In order to execute truncate one must have at least db_ddladmin role or permissions to ALTER table.

    _______________________________________________
    www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    (My original reply was much longer, but I lost it due to that stupid SQLServerCentral bug that tends to eat a reply when you worked on it for a long time. And I had forgotten to copy/paste it somewhere safe first. I don't feel like reconstructing that long reply, so here's the abridged version)

    Stuart Davies (3/21/2013)


    Thanks for the question - good reminder of the basics.

    It appears that quite a few here needed reminding of the differences - 40% wrong at the moment.

    I'm not at all surprised. The percentages for the identity and trigger subquestions show that almost everyone had them right; the problem is in the DDL/DML part. And rightly so.

    The only reason I got points for this question is that I realized that if the author had considered truncate to be DML, *two* extra options would have been right. And the question texts said to check 3 answers, not 4. But I do disagree with this answer, and I do disagree with the parts in Books Online that describe trunacte table as DDL.

    DDL is language that describes data structures. Truncate table does not in any way change the schema of a table. It only removes all data from a table and (for tables with an identity column) resets the "next value" (not the original seed or the increment) of the identity property.

    DML is language that inserted, updates, or deletes data. That (deleting data) is exactly what TRUNCATE TABLE does!


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Toreador

    SSChampion

    Points: 11225

    "Data Definition Language (DDL) is a vocabulary used to define data structures in SQL Server 2012. Use these statements to create, alter, or drop data structures in an instance of SQL Server."

    Even though it then proceeds to include Truncate in the list, by that definition surely it's DML - it's changing the data, not its structure?

  • kapil_kk

    SSC-Insane

    Points: 21316

    well said Hugo 😛

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Hugo Kornelis (3/21/2013)


    ...

    DML is language that inserted, updates, or deletes data. That (deleting data) is exactly what TRUNCATE TABLE does!

    Thank you Hugo; but one doubt...

    .... but still it is does not works like how delete works... this deallocates the pages and not that it goes deleting row by row, so in some mysterious way this actually is re-defining the table object? (as it is not creating or re-creating it from the scratch so the property of the table is reset) so it could be DDL?

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • db4breakfast

    Ten Centuries

    Points: 1097

    I had to think twice about DDL and DML. Maybe it's better to not include (choose 3) in the question. this will make us think more. Otherwise people just use elimination to obtain the answer.

    Thanks for the question.

    --------------------------------------------------------------------------------------
    Hai Ton
    My Db4Breakfast blog.

  • Mike Hays

    SSCommitted

    Points: 1871

    Good Question, made me think before I finished my first cup of coffee.

  • sknox

    SSChampion

    Points: 12216

    paul s-306273 (3/21/2013)


    Two pints for this?

    Wow!

    You got two pints? All I got was points!

    I want my two pints!

  • DBA_Dom

    SSCrazy

    Points: 2792

    Nice question.

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Raghavendra Mudugal (3/21/2013)


    Hugo Kornelis (3/21/2013)


    ...

    DML is language that inserted, updates, or deletes data. That (deleting data) is exactly what TRUNCATE TABLE does!

    Thank you Hugo; but one doubt...

    .... but still it is does not works like how delete works... this deallocates the pages and not that it goes deleting row by row, so in some mysterious way this actually is re-defining the table object? (as it is not creating or re-creating it from the scratch so the property of the table is reset) so it could be DDL?

    That's a detail of how the functionality is implemented. But one of the basics of the theory of relational databases is that only functionality matters, not implementation.

    However, you do have a point when you bring up that similarity between TRUNCATE and DROP/CREATE. If you consider TRUNCATE as a shorthand for dropping the table and the recreating it with the same schema, it would indeed be DDL.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • sknox

    SSChampion

    Points: 12216

    Hugo Kornelis (3/21/2013)


    ...

    DDL is language that describes data structures. Truncate table does not in any way change the schema of a table. It only removes all data from a table and (for tables with an identity column) resets the "next value" (not the original seed or the increment) of the identity property.

    That's the effect of DDL. But the mechanism is to act on the metadata of the table, not on the data pages.

    DML is language that inserted, updates, or deletes data. That (deleting data) is exactly what TRUNCATE TABLE does!

    Technically, no. TRUNCATE TABLE resets the metadata that assigns pages of data to the table. It doesn't directly delete the data; that's a side effect of what it actually does.

    Mechanically, TRUNCATE TABLE is DDL: It doesn't act on the row data, but on the metadata (this is why you need ddladmin rights.)

    But functionally it's DML: its function is to delete data -- except when you get to the identity thing: that's DDL again.

    So really, it's both: it's a DDL mechanism with a DML purpose.

  • Mike Is Here

    Hall of Fame

    Points: 3348

    Thanks for the question.

  • Nadrek

    SSC-Insane

    Points: 20039

    I also had to think about DML vs. DDL, because TRUNCATE is more or less equivalent to:

    DELETE FROM + Reset Identity

    and

    DROP TABLE(etc.) + CREATE TABLE(etc.)

    For me, the clincher was the ALTER TABLE permissions requirements of TRUNCATE - which I personally absolutely disagree with. I'd much rather see users able to use TRUNCATE based on perhaps

    DELETE permission plus a new RESET IDENTITY permission, which being prohibited from creating tables, dropping tables, adding columns, redefining columns, etc.

  • mtassin

    SSC-Insane

    Points: 23035

    Dineshbabu (3/20/2013)


    Thanks for recalling the basics.

    Still I have question, why it has been called as DDL command?

    It might have to do with requiring ALTER TABLE permissions to use it?

    Not sure... it's why I picked DDL, the other two I knew... but that one made me sweat.

    good question.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

Viewing 15 posts - 16 through 30 (of 45 total)

You must be logged in to reply to this topic. Login to reply