TRUNCATE Table - SQL Server Brain Basher of the Week #060

Here is a simple question I asked from an candidate I interviewed last week. This was not the exact topic discussed but while discussing something else, I asked based on one his explanations. It is not about differences between TRUNCATE and DELETE or advantages over another, this is what I asked;

Is TRUNCATE statement is a DML statement or DDL statement?

Unfortunately I did not get the answer I need. First of all let's try to understand what is DML and DDL;

• DML - Data Manipulation Language - most action queries come under this and most common ones are INSERT, UPDATE and DELETE. There is another category called DQL - Data Query Language that is the category for commands like SELECT but some consider the SELECT is a part of DML.
• DDL - Data Definition Language - Commands that modify objects in the database come under this. Statement like CREATE TABLE, CREATE USER are example of this.

Now, TRUNCATE is DML or DDL?

It is a DDL command because it does not work with data (like DELETE command). Yes, it deletes all records but it actually does a operation similar to drop/re-create operation hence it is considered as a DDL command.

Remember, DDL does following;

• Removes all data pages and no empty-pages are left behind.
• Acquires only table or page level locks.
• Resets identity property.
• Needs Alter permission.