SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TRUNCATE


TRUNCATE

Author
Message
Peter Trast
Peter Trast
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1086 Visits: 655
Paul White NZ (3/31/2010)
Peter Trast (3/31/2010)
Hey, people from middle earth can't boo! :-D

Middle Earth, if you please :-P


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
Clive Chinery
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1443 Visits: 154
Continuing on the humour on this thread, please see http://apod.nasa.gov/apod/ap100401.html
Christian Buettner-167247
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5365 Visits: 3889
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 Smile

Best Regards,

Chris Büttner
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36614 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
vk-kirov
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4360 Visits: 4408
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
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36614 Visits: 11361
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 :-D
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
SQLblog.com
@SQL_Kiwi
Peter Trast
Peter Trast
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1086 Visits: 655
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
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4360 Visits: 4408
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
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36614 Visits: 11361
...and we are back to the duck/dog duality principle Laugh



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Peter Trast
Peter Trast
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1086 Visits: 655
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...... :-D

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search