March 31, 2010 at 9:00 am
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
April 1, 2010 at 1:11 am
Continuing on the humour on this thread, please see http://apod.nasa.gov/apod/ap100401.html
April 6, 2010 at 1:55 am
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
April 6, 2010 at 2:27 am
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.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 6, 2010 at 1:09 pm
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.
April 6, 2010 at 1:39 pm
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:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 6, 2010 at 1:47 pm
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...
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
April 7, 2010 at 1:21 am
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)
April 7, 2010 at 2:06 am
...and we are back to the duck/dog duality principle :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 7, 2010 at 10:10 am
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
April 7, 2010 at 1:40 pm
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 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply