InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 09:44:20 GMT20http://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]Peter Trast (4/7/2010)[/b][hr][quote][b]vk-kirov (4/7/2010)[/b][hr]Maybe because TRUNCATE manipulates metadata, not data? (It's my suggestion only)[/quote]DOES truncate delete metadata??? (I am thinking no).[/quote]You are right. TRUNCATE does not [i]delete[/i] metadata, it [i]manipulates[/i] metadata. But... what is the relation between your (rhetorical) question and my suggestion? :-)Let's see what exactly TRUNCATE does ([url]http://msdn.microsoft.com/en-us/library/ms177570.aspx[/url]):[quote]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.[/quote]Page deallocation is surely a metadata manipulation. Data remain intact in their comfortable pages... until the time comes. :Whistling:[quote][b]Peter Trast (4/7/2010)[/b][hr]I guess I was suggesting that the person who authored that webpage didn't know what he was talking about :-)[/quote]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 :-)Wed, 07 Apr 2010 13:40:12 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]vk-kirov (4/7/2010)[/b][hr][quote][b]Paul White NZ (4/6/2010)[/b][hr]You posted that link and statement before![/quote]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 :-) )[quote][b]Peter Trast (4/6/2010)[/b][hr]What is TRUNCATE if not a DML statement by that definition?[/quote]Maybe because TRUNCATE manipulates metadata, not data? (It's my suggestion only)[/quote]Phil: Well, what if there is no tomorrow? There wasn't one today...... :-DDOES 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... lolWed, 07 Apr 2010 10:10:22 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx...and we are back to the duck/dog duality principle :laugh:Wed, 07 Apr 2010 02:06:20 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]Paul White NZ (4/6/2010)[/b][hr]You posted that link and statement before![/quote]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 :-) )[quote][b]Peter Trast (4/6/2010)[/b][hr]What is TRUNCATE if not a DML statement by that definition?[/quote]Maybe because TRUNCATE manipulates metadata, not data? (It's my suggestion only)Wed, 07 Apr 2010 01:21:07 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]Paul White NZ (4/6/2010)[/b][hr][quote][b]Christian Buettner-167247 (4/6/2010)[/b][hr]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.[/quote]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.[/quote]The quote button is quoting the wrong post...@vk-kirovThe website you linked says:Data manipulation language (DML) statements access and manipulate [b]data[/b] (my emphasis) in existing schema objects. What is TRUNCATE if not a DML statement by that definition? :-)Tue, 06 Apr 2010 13:47:28 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]vk-kirov (4/6/2010)[/b][hr][quote][b]Paul White NZ (4/6/2010)[/b][hr]The primary purpose of TRUNCATE TABLE is to delete records - clearly a DML.[/quote]Not so clear to Oracle guys :-)[url]http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_1001.htm[/url] – TRUNCATE is in the DDL list.[/quote]Groundhog day in here :-DYou posted that link and statement before!If this were an Oracle site, I would tend to agree with you. As it is...:-PAll 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:Tue, 06 Apr 2010 13:39:23 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]Paul White NZ (4/6/2010)[/b][hr]The primary purpose of TRUNCATE TABLE is to delete records - clearly a DML.[/quote]Not so clear to Oracle guys :-)[url]http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_1001.htm[/url] – TRUNCATE is in the DDL list.Tue, 06 Apr 2010 13:09:26 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]Christian Buettner-167247 (4/6/2010)[/b][hr]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.[/quote]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.Tue, 06 Apr 2010 02:27:23 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]Paul White NZ (3/30/2010)[/b][hr][quote][b]vk-kirov (2/10/2010)[/b][hr]In the example above, we will see request_mode = Sch-M (schema modification). This is specific to DDL events.[/quote]Not true. From Books Online - [url=http://msdn.microsoft.com/en-us/library/ms175519.aspx]Lock Modes[/url]:[color="#0000FF"][quote][i]Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.[/i][/quote][/color][b]So, table truncation is explicitly defined as a DML operation.[/b][/quote]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 :)Tue, 06 Apr 2010 01:55:11 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspxContinuing on the humour on this thread, please see [url=http://apod.nasa.gov/apod/ap100401.html]http://apod.nasa.gov/apod/ap100401.html[/url]Thu, 01 Apr 2010 01:11:07 GMTClive Chineryhttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]Paul White NZ (3/31/2010)[/b][hr][quote][b]Peter Trast (3/31/2010)[/b][hr]Hey, people from middle earth can't boo! :-D[/quote]Middle Earth, if you please :-P[/quote]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.Wed, 31 Mar 2010 09:00:41 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]Peter Trast (3/31/2010)[/b][hr]Hey, people from middle earth can't boo! :-D[/quote]Middle Earth, if you please :-PWed, 31 Mar 2010 08:51:44 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]Paul White NZ (3/31/2010)[/b][hr][quote][b]Peter Trast (3/31/2010)[/b][hr][quote][b]Paul White NZ (3/31/2010)[/b][hr][quote][b]vk-kirov (3/31/2010)[/b][hr]Nice find, thank you. So, if it barks like a dog, it behaves like a dog, what is it?...[/quote]It is a duck, with some arguably dog-like features :-P[/quote]Interesting de-duck-tion....[/quote]Boooooo! :-P[/quote]Hey, people from middle earth can't boo! :-DWed, 31 Mar 2010 08:44:43 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]Peter Trast (3/31/2010)[/b][hr][quote][b]Paul White NZ (3/31/2010)[/b][hr][quote][b]vk-kirov (3/31/2010)[/b][hr]Nice find, thank you. So, if it barks like a dog, it behaves like a dog, what is it?...[/quote]It is a duck, with some arguably dog-like features :-P[/quote]Interesting de-duck-tion....[/quote]Boooooo! :-PWed, 31 Mar 2010 08:19:10 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]Paul White NZ (3/31/2010)[/b][hr][quote][b]vk-kirov (3/31/2010)[/b][hr]Nice find, thank you. So, if it barks like a dog, it behaves like a dog, what is it?...[/quote]It is a duck, with some arguably dog-like features :-P[/quote]Interesting de-duck-tion....Wed, 31 Mar 2010 08:08:21 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]vk-kirov (3/31/2010)[/b][hr]Nice find, thank you. So, if it barks like a dog, it behaves like a dog, what is it?...[/quote]It is a duck, with some arguably dog-like features :-PWed, 31 Mar 2010 01:52:38 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]Paul White NZ (3/30/2010)[/b][hr][color="#0000FF"][quote][i]Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.[/i][/quote][/color][b]So, table truncation is explicitly defined as a DML operation.[/b][/quote]Nice find, thank you.So, if it barks like a dog, it behaves like a dog, what is it?.....it may be a duck in SQL Server :-DWed, 31 Mar 2010 00:53:19 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]Paul White NZ (3/30/2010)[/b][hr][quote][b]Peter Trast (3/30/2010)[/b][hr]Impeccable logic Paul ;-)[/quote]Given the bird theme, I suppose it should be [i]im-peck-able[/i] logic really :-D(sorry)[/quote]Booooo.... lol...Tue, 30 Mar 2010 13:54:35 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]Peter Trast (3/30/2010)[/b][hr]Impeccable logic Paul ;-)[/quote]Given the bird theme, I suppose it should be [i]im-peck-able[/i] logic really :-D(sorry)Tue, 30 Mar 2010 13:49:28 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]Paul White NZ (3/30/2010)[/b][hr][quote][b]vk-kirov (3/3/2010)[/b][hr]So, db_ddladmin can run any DDL command; db_ddladmin can run TRUNCATE TABLE. This means TRUNCATE is a DDL command, I believe :-)[/quote]Crows are birds.All crows are black.Therefore, all birds are black.Oh dear.:laugh:[/quote]Impeccable logic Paul ;-)Tue, 30 Mar 2010 13:42:23 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]vk-kirov (2/10/2010)[/b][hr]In the example above, we will see request_mode = Sch-M (schema modification). This is specific to DDL events.[/quote]Not true. From Books Online - [url=http://msdn.microsoft.com/en-us/library/ms175519.aspx]Lock Modes[/url]:[color="#0000FF"][quote][i]Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.[/i][/quote][/color][b]So, table truncation is explicitly defined as a DML operation.[/b]Tue, 30 Mar 2010 09:10:14 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]vk-kirov (3/3/2010)[/b][hr]So, db_ddladmin can run any DDL command; db_ddladmin can run TRUNCATE TABLE. This means TRUNCATE is a DDL command, I believe :-)[/quote]Crows are birds.All crows are black.Therefore, all birds are black.Oh dear.:laugh:Tue, 30 Mar 2010 09:01:38 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]petertrast (3/3/2010)[/b][hr][quote][b]Christian Buettner-167247 (2/5/2010)[/b][hr]I wonder why there is no DDL trigger available to capture this of event...[/quote]Because it is a DML event, the structure of the table is not changing, just the data in it?[/quote]By the way, Oracle documentation contains an explicit list of DDL statements: [url]http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_1001.htm#sthref2301[/url]. TRUNCATE is in that list. So, at least in Oracle it is not a DML event.Unfortunately, I didn't find the similar list for MSSQL. However, these two quotes from BOL implicitly say that TRUNCATE belongs to DDL:[url]http://msdn.microsoft.com/en-us/library/ms177570.aspx[/url][quote][b]TRUNCATE TABLE (Transact-SQL)[/b]Permissions--------------------------------------------------------------------------------The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles[/quote]OK, who's that guy with db_ddladmin permissions? [url]http://msdn.microsoft.com/en-us/library/ms189121.aspx[/url][quote]Database-level role name: db_ddladmin Description: Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.[/quote]So, db_ddladmin can run any DDL command; db_ddladmin can run TRUNCATE TABLE. This means TRUNCATE is a DDL command, I believe :-)Wed, 03 Mar 2010 13:43:36 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]petertrast (3/3/2010)[/b][hr][quote][b]SanjayAttray (2/6/2010)[/b][hr]Back to basics. Most of the time I select wrong answer for basic and simple question. Triggers are for INSERT, UPDATE, or DELETE only.[/quote]For DML, yes...Or... CREATE, ALTER, etc.... for DDL...[/quote]OK, I promise to read all posts before replying :-DWed, 03 Mar 2010 11:18:11 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]SanjayAttray (2/6/2010)[/b][hr]Back to basics. Most of the time I select wrong answer for basic and simple question. Triggers are for INSERT, UPDATE, or DELETE only.[/quote]For DML, yes...Or... CREATE, ALTER, etc.... for DDL...Wed, 03 Mar 2010 11:16:16 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]Christian Buettner-167247 (2/5/2010)[/b][hr]I wonder why there is no DDL trigger available to capture this of event...[/quote]Because it is a DML event, the structure of the table is not changing, just the data in it? Sound right experts?Wed, 03 Mar 2010 11:13:21 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]SanjayAttray (2/6/2010)[/b][hr]Triggers are for INSERT, UPDATE, or DELETE only.[/quote]Correction:[b]DML[/b] triggers are for INSERT, UPDATE, or DELETE only.There are also DDL triggers for a wide variety of DDL events. I answered wrong because I thought that TRUNCATE could generate a DDL event :-)Actually TRUNCATE looks like a DDL statement, because it requests a schema modification lock:[code="sql"]CREATE TABLE test (a INT)BEGIN TRANSACTIONTRUNCATE TABLE testSELECT *FROM sys.dm_tran_locksWHERE request_session_id = @@SPID AND resource_type = 'OBJECT' AND resource_associated_entity_id = OBJECT_ID('test')ROLLBACKDROP TABLE test[/code]In the example above, we will see request_mode = Sch-M (schema modification). This is specific to DDL events.Wed, 10 Feb 2010 03:28:19 GMTvk-kirovhttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspxhey Clive...good catch..:blink::cool:Mon, 08 Feb 2010 23:00:33 GMTjshailendrahttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx"TRUE or FASLE" - good question but in need of a spell check!A simple experiment with "fasle" shows that the spell check ignores words in capitals!Mon, 08 Feb 2010 22:43:21 GMTClive Chineryhttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote]Triggers are for INSERT, UPDATE, or DELETE only.[/quote]That is true, but not (I think) the purpose of today's QotD.DELETE operates on the individual rows of a table, whereas TRUNCATE operates on the data pages. As each is logged differently there is no information about individual rows for the trigger to reference upon a TRUNCATE statement.Mon, 08 Feb 2010 19:17:59 GMTFalhttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspxThen, is there any way to fire the Trigger upon this Truncate action, as well?[i]In ' Thoughts ',[font="Comic Sans MS"][b]Lonely Vampire[/b][/font][/i]Mon, 08 Feb 2010 00:28:15 GMTLonely Roguehttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspxBack to basics. Most of the time I select wrong answer for basic and simple question. Triggers are for INSERT, UPDATE, or DELETE only.Sat, 06 Feb 2010 19:55:29 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspx[quote][b]barb.wendling (2/5/2010)[/b][hr]yikes - I knew the answer and selected the wrong button -need more coffee :-D[/quote]aaaaaaaaaaaaaaaaaah we are in same team buddy, same happened to me too!!! - :w00t::hehe::w00t::hehe::w00t::hehe::cool:;-):-P:-P:-D:-)Fri, 05 Feb 2010 14:42:49 GMTDugihttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspxyikes - I knew the answer and selected the wrong button -need more coffee :-DFri, 05 Feb 2010 08:33:52 GMTBarbWhttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspxeeeeeeeeeeeeeeeeeeee typing wrong answer this happened that I misunderstand in opposite way :w00t::w00t::w00t:Fri, 05 Feb 2010 06:47:10 GMTDugihttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspxHmmm...I would have thought TRUNCATE could raise a DDL trigger.Fri, 05 Feb 2010 06:42:59 GMTHenry_Leehttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspxGood spin on what is and is not recorded in the transaction log.Fri, 05 Feb 2010 04:09:47 GMTStewart "Arturius" Campbellhttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspxI wonder why there is no DDL trigger available to capture this of event...Fri, 05 Feb 2010 01:23:09 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspxthanks for the question.Thu, 04 Feb 2010 22:45:21 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic860154-2605-1.aspxthis was a very easy question...:-DThu, 04 Feb 2010 22:41:08 GMTziangij