Pages

Tuesday, November 19, 2013

Is Truncate DDL or DML?

Recently as I referred to the book of Microsoft SQL Server 2012 T-SQL Fundamentals by Ben-Gan, Itzik for DML definitions, there is a conclusion about TRUNCATE statement that surprised me at first:
"Another common misunderstanding is that TRUNCATE is a DDL statement, but in fact it is a DML statement."
Yes. I had always had that “misunderstanding” to regard TRUNCATE as DDL because TRUNCATE statement removes the data by deallocating pages used to store the table and records only the page deallocations in the transaction log. It is not simply row removal as DELETE statement.

After further discussion with the author (I really appreciated that Itzik spent time to help me understand his conclusion.), it turned out TRUNCATE is listed under Data Manipulation Language (DML) in the standard document ISO_9075_02_Foundation_2011_E. Also, the Standard ISO/ANSI SQL deals with only the logical aspects of the code, and logically TRUNCATE statement deletes all rows from a table.

At the same time, Microsoft listed TRUNCATE TABLE as Data Definition Language (DDL) Statements together with ALTER, CREATE, DISABLE/ENABLE TRIGGER, DROP, and UPDATE STATISTICS.

Isn’t it interesting to know about that?

No comments:

Post a Comment