TRUNCATE

  • Peter Trast

    SSCarpal Tunnel

    Points: 4332

    Paul White NZ (3/31/2010)


    Peter Trast (3/31/2010)


    Hey, people from middle earth can't boo! πŸ˜€

    Middle Earth, if you please πŸ˜›

    That was on purpose. I know how much the hobbits hate that... it's hard not to try to irritate those perpetually smiling little devils.

    Peter Trast
    Microsoft Certified ...(insert many literal strings here)
    Microsoft Design Architect with Alexander Open Systems

  • Clive Chinery

    SSCrazy

    Points: 2563

    Continuing on the humour on this thread, please see http://apod.nasa.gov/apod/ap100401.html

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    Paul White NZ (3/30/2010)


    vk-kirov (2/10/2010)


    In the example above, we will see request_mode = Sch-M (schema modification). This is specific to DDL events.

    Not true. From Books Online - Lock Modes:

    Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.

    So, table truncation is explicitly defined as a DML operation.

    Well, this would not be the first time that there is incorrect documentation out there in MSDN.

    From what I undestand, the schema lock is aquired to for the identity re-seed. So one question is now: do you see the identity seed as part of the data or as part of the DDL? I would consider it DDL.

    Maybe TRUNCATE is just DMODLOMB (Data Manipulation Or Definition Language Or Maybe Both).

    This might be a reason why they have not explicitly specified the type directly in the TRUNCATE topic in MSDN - they might not know for sure either πŸ™‚

    Best Regards,

    Chris BΓΌttner

  • Paul White

    SSC Guru

    Points: 150442

    Christian Buettner-167247 (4/6/2010)


    Well, this would not be the first time that there is incorrect documentation out there in MSDN. From what I undestand, the schema lock is aquired to for the identity re-seed. So one question is now: do you see the identity seed as part of the data or as part of the DDL? I would consider it DDL.

    A Sch-M lock is certainly required for a RESEED, but not every table has an IDENTITY column :w00t:

    So, that cannot be the general explanation.

    As the documentation states, the Sch-M lock is used to prevent concurrent access to the table while the operation progresses. The primary purpose of TRUNCATE TABLE is to delete records - clearly a DML. The fact that an Sch-M lock is taken is an implementation detail.

  • vk-kirov

    SSCertifiable

    Points: 7686

    Paul White NZ (4/6/2010)


    The primary purpose of TRUNCATE TABLE is to delete records - clearly a DML.

    Not so clear to Oracle guys πŸ™‚

    http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_1001.htm – TRUNCATE is in the DDL list.

  • Paul White

    SSC Guru

    Points: 150442

    vk-kirov (4/6/2010)


    Paul White NZ (4/6/2010)


    The primary purpose of TRUNCATE TABLE is to delete records - clearly a DML.

    Not so clear to Oracle guys πŸ™‚

    http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_1001.htm – TRUNCATE is in the DDL list.

    Groundhog day in here πŸ˜€

    You posted that link and statement before!

    If this were an Oracle site, I would tend to agree with you. As it is...:-P

    All joking aside, let me clarify: The primary purpose of TRUNCATE TABLE (the T-SQL statement) is to delete rows, which is clearly a DML operation (in my view).

    Oracle users can make their own arrangements :hehe:

  • Peter Trast

    SSCarpal Tunnel

    Points: 4332

    Paul White NZ (4/6/2010)


    Christian Buettner-167247 (4/6/2010)


    Well, this would not be the first time that there is incorrect documentation out there in MSDN. From what I undestand, the schema lock is aquired to for the identity re-seed. So one question is now: do you see the identity seed as part of the data or as part of the DDL? I would consider it DDL.

    A Sch-M lock is certainly required for a RESEED, but not every table has an IDENTITY column :w00t:

    So, that cannot be the general explanation.

    As the documentation states, the Sch-M lock is used to prevent concurrent access to the table while the operation progresses. The primary purpose of TRUNCATE TABLE is to delete records - clearly a DML. The fact that an Sch-M lock is taken is an implementation detail.

    The quote button is quoting the wrong post...

    @vk-kirov

    The website you linked says:

    Data manipulation language (DML) statements access and manipulate data (my emphasis) in existing schema objects.

    What is TRUNCATE if not a DML statement by that definition? πŸ™‚

    Peter Trast
    Microsoft Certified ...(insert many literal strings here)
    Microsoft Design Architect with Alexander Open Systems

  • vk-kirov

    SSCertifiable

    Points: 7686

    Paul White NZ (4/6/2010)


    You posted that link and statement before!

    Oops... did I?.. :blush:

    I just want to say that your conclusion "if a command deletes rows, it's a DML" may be true in some cases (MSSQL, Sybase, ...) and false in other cases (Oracle, ...). (I hope I didn't post this statement before πŸ™‚ )

    Peter Trast (4/6/2010)


    What is TRUNCATE if not a DML statement by that definition?

    Maybe because TRUNCATE manipulates metadata, not data? (It's my suggestion only)

  • Paul White

    SSC Guru

    Points: 150442

    ...and we are back to the duck/dog duality principle :laugh:

  • Peter Trast

    SSCarpal Tunnel

    Points: 4332

    vk-kirov (4/7/2010)


    Paul White NZ (4/6/2010)


    You posted that link and statement before!

    Oops... did I?.. :blush:

    I just want to say that your conclusion "if a command deletes rows, it's a DML" may be true in some cases (MSSQL, Sybase, ...) and false in other cases (Oracle, ...). (I hope I didn't post this statement before πŸ™‚ )

    Peter Trast (4/6/2010)


    What is TRUNCATE if not a DML statement by that definition?

    Maybe because TRUNCATE manipulates metadata, not data? (It's my suggestion only)

    Phil: Well, what if there is no tomorrow? There wasn't one today...... πŸ˜€

    DOES truncate delete metadata??? (I am thinking no). I guess I was suggesting that the person who authored that webpage didn't know what he was talking about πŸ™‚ Or it could just be me, it HAS been known to happen... lol

    Peter Trast
    Microsoft Certified ...(insert many literal strings here)
    Microsoft Design Architect with Alexander Open Systems

  • vk-kirov

    SSCertifiable

    Points: 7686

    Peter Trast (4/7/2010)


    vk-kirov (4/7/2010)


    Maybe because TRUNCATE manipulates metadata, not data? (It's my suggestion only)

    DOES truncate delete metadata??? (I am thinking no).

    You are right. TRUNCATE does not delete metadata, it manipulates metadata. But... what is the relation between your (rhetorical) question and my suggestion? πŸ™‚

    Let's see what exactly TRUNCATE does (http://msdn.microsoft.com/en-us/library/ms177570.aspx):

    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.

    TRUNCATE TABLE always locks the table and page but not each row.

    Page deallocation is surely a metadata manipulation. Data remain intact in their comfortable pages... until the time comes. :Whistling:

    Peter Trast (4/7/2010)


    I guess I was suggesting that the person who authored that webpage didn't know what he was talking about πŸ™‚

    Perhaps the definitions of DML/DDL on that page are slightly inaccurate.

    But there's a dramatical difference between DML and DDL statements in Oracle. A DDL statement is committed implicitly and immediately, and cannot be rolled back. This is why it's very important to know the classification of the SQL commands.

    Happily, we are using SQL Server and can roll back any DDL command; so I consider the question "is TRUNCATE a DDL statement or not?" as a theoretical question. Happily, Paul gave the straightforward answer to this question: it's a DML statement because Microsoft documentation says it. Other arguments may be (and have been) debated at least on two forum pages. Even using the de-duck-tion methods πŸ™‚

Viewing 11 posts - 31 through 41 (of 41 total)

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