Delete v Truncate
-
Stuart Davies (3/21/2013)
Thanks for the question - good reminder of the basics.It appears that quite a few here needed reminding of the differences - 40% wrong at the moment.
I wonder how much higher it would have been if you asked which are logged in the question :w00t:
+1
well said Stuart..
What about which can be Rollbacked 🙂
Lot of ppl have confusion over this as well 😀
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Is there an official list of DDL operations or is its DDL-ness decided by community consensus? 🙂
http://msdn.microsoft.com/en-us/library/ff848799.aspx
Also, it can be checked by user rights. Members of db_datawriter role can run DELETE command, but can not TRUNCATE. In order to execute truncate one must have at least db_ddladmin role or permissions to ALTER table.
_______________________________________________
www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore) -
(My original reply was much longer, but I lost it due to that stupid SQLServerCentral bug that tends to eat a reply when you worked on it for a long time. And I had forgotten to copy/paste it somewhere safe first. I don't feel like reconstructing that long reply, so here's the abridged version)
Stuart Davies (3/21/2013)
Thanks for the question - good reminder of the basics.It appears that quite a few here needed reminding of the differences - 40% wrong at the moment.
I'm not at all surprised. The percentages for the identity and trigger subquestions show that almost everyone had them right; the problem is in the DDL/DML part. And rightly so.
The only reason I got points for this question is that I realized that if the author had considered truncate to be DML, *two* extra options would have been right. And the question texts said to check 3 answers, not 4. But I do disagree with this answer, and I do disagree with the parts in Books Online that describe trunacte table as DDL.
DDL is language that describes data structures. Truncate table does not in any way change the schema of a table. It only removes all data from a table and (for tables with an identity column) resets the "next value" (not the original seed or the increment) of the identity property.
DML is language that inserted, updates, or deletes data. That (deleting data) is exactly what TRUNCATE TABLE does!
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
nenad-zivkovic (3/21/2013)
http://msdn.microsoft.com/en-us/library/ff848799.aspx"Data Definition Language (DDL) is a vocabulary used to define data structures in SQL Server 2012. Use these statements to create, alter, or drop data structures in an instance of SQL Server."
Even though it then proceeds to include Truncate in the list, by that definition surely it's DML - it's changing the data, not its structure?
-
well said Hugo 😛
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Hugo Kornelis (3/21/2013)
...DML is language that inserted, updates, or deletes data. That (deleting data) is exactly what TRUNCATE TABLE does!
Thank you Hugo; but one doubt...
.... but still it is does not works like how delete works... this deallocates the pages and not that it goes deleting row by row, so in some mysterious way this actually is re-defining the table object? (as it is not creating or re-creating it from the scratch so the property of the table is reset) so it could be DDL?
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
I had to think twice about DDL and DML. Maybe it's better to not include (choose 3) in the question. this will make us think more. Otherwise people just use elimination to obtain the answer.
Thanks for the question.
--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog. -
Good Question, made me think before I finished my first cup of coffee.
-
paul s-306273 (3/21/2013)
Two pints for this?Wow!
You got two pints? All I got was points!
I want my two pints!
-
Nice question.
-
Raghavendra Mudugal (3/21/2013)
Hugo Kornelis (3/21/2013)
...DML is language that inserted, updates, or deletes data. That (deleting data) is exactly what TRUNCATE TABLE does!
Thank you Hugo; but one doubt...
.... but still it is does not works like how delete works... this deallocates the pages and not that it goes deleting row by row, so in some mysterious way this actually is re-defining the table object? (as it is not creating or re-creating it from the scratch so the property of the table is reset) so it could be DDL?
That's a detail of how the functionality is implemented. But one of the basics of the theory of relational databases is that only functionality matters, not implementation.
However, you do have a point when you bring up that similarity between TRUNCATE and DROP/CREATE. If you consider TRUNCATE as a shorthand for dropping the table and the recreating it with the same schema, it would indeed be DDL.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hugo Kornelis (3/21/2013)
...DDL is language that describes data structures. Truncate table does not in any way change the schema of a table. It only removes all data from a table and (for tables with an identity column) resets the "next value" (not the original seed or the increment) of the identity property.
That's the effect of DDL. But the mechanism is to act on the metadata of the table, not on the data pages.
DML is language that inserted, updates, or deletes data. That (deleting data) is exactly what TRUNCATE TABLE does!
Technically, no. TRUNCATE TABLE resets the metadata that assigns pages of data to the table. It doesn't directly delete the data; that's a side effect of what it actually does.
Mechanically, TRUNCATE TABLE is DDL: It doesn't act on the row data, but on the metadata (this is why you need ddladmin rights.)
But functionally it's DML: its function is to delete data -- except when you get to the identity thing: that's DDL again.
So really, it's both: it's a DDL mechanism with a DML purpose.
-
Thanks for the question.
-
I also had to think about DML vs. DDL, because TRUNCATE is more or less equivalent to:
DELETE FROM + Reset Identity
and
DROP TABLE(etc.) + CREATE TABLE(etc.)
For me, the clincher was the ALTER TABLE permissions requirements of TRUNCATE - which I personally absolutely disagree with. I'd much rather see users able to use TRUNCATE based on perhaps
DELETE permission plus a new RESET IDENTITY permission, which being prohibited from creating tables, dropping tables, adding columns, redefining columns, etc.
-
Dineshbabu (3/20/2013)
Thanks for recalling the basics.Still I have question, why it has been called as DDL command?
It might have to do with requiring ALTER TABLE permissions to use it?
Not sure... it's why I picked DDL, the other two I knew... but that one made me sweat.
good question.
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply