Sunday, November 27, 2016

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.

3 comments:

  1. can you give a definition for truncated table as well..
    usually i see truncated statement when the data is mismatch with its data type.

    ReplyDelete
  2. Hi,

    I assume that you need to know what is a Truncated Table? If so, you can call a table as a truncated table after performing TRUNCATE TABLE operation (means all records are removed).

    However, the statements (or warning messages) you have see with statements are related to string-truncation. Example, if you try to insert a value with five characters to a column set with varchar(4), then you get this warning.

    Hope I have explained what you need.

    ReplyDelete