SQL Server Truncate a DDL or DML command

uidvcgyl  于 2023-06-21  发布在  其他
关注(0)|答案(5)|浏览(120)

I always knew that TRUNCATE is a DDL command but Microsoft documents are confusing me.

This link says that TRUNCATE is a DDL command and this says that TRUNCATE is a DML command

Also, does clarification of DDL and DML are different in different database? Ex. Oracle, MySql etc.

iyfamqjs

iyfamqjs1#

Personally, I would say that TRUNCATE is a DML command; you're manipulating the data using it, not changing the definition.

There are a few bits on the docs that conflict, mainly as so e are older than others. They can't even decide if CASE is a statement or an expression.

von4xj4u

von4xj4u2#

Wikipedia says TRUNCATE is DML:
In SQL, the TRUNCATE TABLE statement is a Data Manipulation Language (DML) operation that marks the extents of a table for deallocation (empty for reuse). The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms. It was officially introduced in the SQL:2008 standard.

https://en.wikipedia.org/wiki/Truncate_(SQL)

ktecyv1j

ktecyv1j3#

Well, TRUNCATE TABLE, as decribed in Microsoft documentation is similar to DELETE (by their own admission). And DELETE is DML, therefore, TRUNCATE TABLE shold be DML as well.

Perhaps the one who wrote the first article did a mistake putting it there. OR perhaps he/she wanted to point out that it's a command to use with the same caution you use for DDL.

I must admit that it's the first time I see that command, and I don't know if it's included in the SQL standard.

dwthyt8l

dwthyt8l4#

The fact is that TRUNCATE is a DDL command. The first link you provided is correct, the second one was fixed yesterday.

Wikipedia also defines it as a DDL command, but an incorrect edit made on 12 February 2018‎ (and properly reverted on 3 April 2018‎ ) made it say otherwise for a while.

jobtbby3

jobtbby35#

According to the official SQL standards documentation, TRUNCATE TABLE is a data manipulation statement and has been since it was introduced in the 2008 version of the standard. 1

Various database vendors describe TRUNCATE as DDL, apparently following the lead of Oracle who seem to have been the first to refer to it that way. Oracle's classification of TRUNCATE as DDL presumably arose because of their internal implementation of the truncate operation and others continue to apply the DDL label even though they don't implement TRUNCATE the same way as Oracle.

In standard SQL the difference between TRUNCATE and DELETE without a WHERE clause is that TRUNCATE does not invoke any triggered actions. Logically it behaves as data manipulation and in the standard it comes in section 14 along with all other data manipulation language (including DELETE which immediately precedes TRUNCATE). There seems to be no sound reason to call it DDL, it's just a long-standing convention in vendors' documentation.

相关问题