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.