Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

TRUNCATE Expand / Collapse
Author
Message
Posted Wednesday, March 31, 2010 9:00 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, Visits: 655
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
Post #893852
Posted Thursday, April 1, 2010 1:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 2, 2011 3:22 AM
Points: 1,227, Visits: 154
Continuing on the humour on this thread, please see http://apod.nasa.gov/apod/ap100401.html
Post #894471
Posted Tuesday, April 6, 2010 1:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, June 20, 2014 1:29 AM
Points: 2,820, Visits: 3,862
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
Post #897377
Posted Tuesday, April 6, 2010 2:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:24 PM
Points: 11,185, Visits: 11,069
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
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #897392
Posted Tuesday, April 6, 2010 1:09 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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.
Post #897842
Posted Tuesday, April 6, 2010 1:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:24 PM
Points: 11,185, Visits: 11,069
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...
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




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #897862
Posted Tuesday, April 6, 2010 1:47 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, 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
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
Post #897869
Posted Wednesday, April 7, 2010 1:21 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
Paul White NZ (4/6/2010)
You posted that link and statement before!

Oops... did I?..
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)
Post #898231
Posted Wednesday, April 7, 2010 2:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:24 PM
Points: 11,185, Visits: 11,069
...and we are back to the duck/dog duality principle



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #898256
Posted Wednesday, April 7, 2010 10:10 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, June 13, 2014 3:03 PM
Points: 594, Visits: 655
vk-kirov (4/7/2010)
Paul White NZ (4/6/2010)
You posted that link and statement before!

Oops... did I?..
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
Post #898715
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse