3

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.

Rubens Farias
  • 57,174
  • 8
  • 131
  • 162
Zerotoinfinity
  • 6,290
  • 32
  • 130
  • 206
  • Possible duplicate: https://stackoverflow.com/questions/26059855/why-is-truncate-a-ddl-statement – MJH Apr 01 '18 at 19:27
  • 1
    Possible duplicate of [How can Delete be both a DDL and a DML statement](https://stackoverflow.com/questions/42536536/how-can-delete-be-both-a-ddl-and-a-dml-statement) –  Apr 01 '18 at 19:30

5 Answers5

3

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
2

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.

Luca Scarcia
  • 208
  • 1
  • 11
2

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)

MJH
  • 1,710
  • 1
  • 9
  • 19
2

Depends on who you ask. Microsoft, for instance, clearly states that TRUNCATE is a DDL command. The second link you provided was altered to reflect that.

Wikipedia defined it as a DDL command for a long time (since 16 June 2010). There was an edit trying to change it on 12 February 2018‎ but was soon reverted on 3 April 2018‎. It was changed again on 4 June 2023 to define it as a DML command.

As @nvogel pointed in the comments, it has originally been categorized as a DML command. Even if you simply compare the two names, Data Manipulation Language (DML) versus Data Definition Language (DDL), it makes sense to classify it as the former.

Marcos Dimitrio
  • 6,651
  • 5
  • 38
  • 62
  • The ISO SQL standard defines TRUNCATE as data manipulation language and always has done. The illogical idea that TRUNCATE is a DDL operation seems to originate with Oracle. Oracle introduced TRUNCATE before it was added to the standard in 2008 and for some reason Microsoft seem to have followed Oracle's lead in classifying TRUNCATE as DDL. – nvogel Jun 26 '23 at 01:41
  • Makes sense, I updated my answer to better reflect that. – Marcos Dimitrio Jun 27 '23 at 02:29
1

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.

nvogel
  • 24,981
  • 1
  • 44
  • 82