THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Dejan Sarka

Truncate Table – DDL or DML Statement?

Many times, categories of concepts and things overlap. It can be hard to categorize some items in a single category. The SQL TRUNCATE TABLE statement is an example of an item that is not so easy to categorize. Is it a DDL (Data Definition Language) or DML (Data Manipulation Language) statement?

There is an ongoing discussion about this topic. However, if you quickly bingle for this question, you get the impression that the majority is somehow leaning more toward defining the TRUNCATE TABLE statement as a DDL statement. For example, Wikipedia clearly states: “In SQL, the TRUNCATE TABLE statement is a Data Definition Language (DDL) operation that marks the extents of a table for deallocation (empty for reuse).” Disclaimer: please note that I do not find Wikipedia as the “ultimate, trustworthy source” – I prefer sources that are signed!

Some of the reasons why many people define the statement as a DDL statement include:

  • It requests schema locks in some systems
  • It is not possible to rollback it in some systems
  • It does not include a WHERE clause
  • It does not fire triggers in some systems
  • It resets the autonumbering column value in some systems
  • It deallocates system pages directly, not through an internal table operation
  • and more.

On the other hand, it looks like there is only one reason to treat the statement as a DML statement:

  • Logically, you just get rid of the data, like with the DELETE statement.

Even the Wikipedia article that I referred to says “The TRUNCATE TABLE mytable statement is logically (though not physically) equivalent to the DELETE FROM mytable statement (without a WHERE clause).”

Like many times, I have to disagree with the majority. I understand that the categorization is somehow confusing, and might even be overlapping. However, the only reason for categorizing the TRUNCATE TABLE statement in the DML category is “THE” reason in my understanding. One of the most important ideas in the Relational Model is the separation between the logical and the physical level. We, users, or people, if you wish, are manipulating with data on the logical level; the physical implementation is left to the database management system. And this is the important part – logically, when you truncate table, you don’t care how this statement is implemented internally, you just want to get rid of the data. It really does not matter what kind of locks a system uses, does it allow WHERE clause or not, etc. The logical point is what matters. Therefore, I would categorize the TRUNCATE TABLE statement as a DML statement.

Of course, this is a purely theoretical question, and is really not important for your practical implementation. As long as your app is doing what it should do, you don’t care too much about these nuances. However, IMO in general there is not enough of theoretical knowledge spread around, and therefore it makes sense to try to get the correct understanding.

But there is always a “but”. Of course, I have immediately another question. What about the ALTER TABLE mytable SWITCH [PARTITION…] TO… statement? ALTER statements have been defined as DDL statements forever. however, again, logically you are just moving the data from one table to another. Therefore – what? What do you think?

Published Friday, March 28, 2014 11:47 AM by Dejan Sarka

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Kennie Nybo Pontoppidan said:

Alter table switch partition should imho not really be categorized as a sql command since it only deals with the physical level. Similarly with truncate. The physical part of deleting data should ideally be hidden from the user of sql

K

March 28, 2014 6:07 AM
 

Dejan Sarka said:

Kennie, thank zou for the comment. Yet, I have to ask - when you say that these commands should not be a part of SQL, you mean "a part of ANSI SQL standard"?

I am not talking about the standard, and I don't really care to much whether this is a part of ANSI SQL or not. It is a good thing to have a standard, and that's it.

What I really like to do is to explain things logically. And I am the first target of this explanation. I start explaining to myself, because I want to understand things from the logical perspective.

Note that I already have a logical explanation for myself for the ALTER TABLE SWITCH [PARTITION] command. I am just no willing it to share yet, I have to think it over couple of times more.

March 28, 2014 7:27 AM
 

TiborKaraszi said:

I've never considered TRUNCATE TABLE could be seen as a DDL command. It doesn't "define" any data structure. Rather it manipulates data. I'm all with you there. (As an aside, it is listed under "data manipulation" in SQL 2011 standard.)

I think that DDL is polluted. Do we define the (logical (physical?)) data structure with CREATE TABLE? Sure. CREATE INDEX? Yes, if we include physical. CREATE USER? No, not really. So the "DDL" term is a bit strange when you think about it, but that won't change so...

As for SWITCH ... I guess I'd go for ... DML. From a purely logical standpoint. But since it lives in the ALTER TABLE command, we'll never get to the point of any universal acceptance for that. So DDL it is, since that it what majority will consider it to be.

March 28, 2014 7:40 AM
 

pmbAustin said:

The one place this distinction matters is in table variables.

You can't do something like this:

DECLARE @tableVar TABLE (columns blah blah)

-- do some stuff

TRUNCATE TABLE @tableVar

The reason offered is due to the fact that TRUNCATE TABLE is a DDL statement, it can't be used on a table variable.

For me there is a slight logical distinction between DELETE FROM MYTABLE and TRUNCATE MYTABLE ... the latter should perform better, and not fire triggers, and not do "row by row" processing... it should literally just throw things away, because I don't care about all that stuff.  If I DID care, I'd be calling DELETE FROM MYTABLE.

March 28, 2014 11:51 AM
 

Dejan Sarka said:

Sorry, pmbAustin, but I fail to see how the fact whether a command works with table variable or not defines the categorization of the command. But thank you for your comment!

March 28, 2014 3:08 PM
 

Lubor said:

TRUNCATE TABLE and SWITCH partition may be considered both DDL and DML. DDL if I'm using only the syntax to categorize, DML if I'm considering the semantics. Some people look only at the syntax and therefore they end up with "DDL".

There is an interesting little inside (dirty?) story behind the ALTER TABLE ... SWITCH related to "DDL or DML". Some participants might have been offended then but I can share it now since the main actors are not working for MSFT any more as well as it is 10 year old affair. Then the SQL dev group had DDL/metadata team responsible for implementing the DDL changes. The team also owned the code. The SWITCH is checking and changing the metadata so it was in this team's court. However, they didn't like the idea at all. If you look at the SWITCH closely you will find out that this is the first time in SQL Server (did I miss any case?) when the metadata is created - or "shuffled over" - the already existing persisted data. The DDL team consider it very dangerous untried territory with great risk and they didn't believe in usefulness of the SWITCH. So after a lot of wrangling inside the SQL group the code was developed and tested by different - DML - team... It is now all back to normal, the metadata team took back the ownership of the code and I think today nobody doubts usefulness of the SWITCH.

By sharing this story I don't want to suggest the SWITCH is "DDL". The implementation place has nothing to do with the categorization. I personally prefer the DML but I'm not taking away the "DDL" from people who believe the syntax is what should be used to determine where it belongs.    

March 28, 2014 8:21 PM
 

Dejan Sarka said:

Lubor, thank you so much for your comment.

I will write a next blog on this topic for the ALTER TABLE SWITCH soon.

March 29, 2014 1:11 AM
 

Koen Verbeeck said:

In order to use the TRUNCATE TABLE statement, you need at least the DDL_ADMIN permissions. So security wise this pleads in favor of the DDL :)

March 29, 2014 10:01 AM
 

brian said:

As developer I would view truncate table as a DML because it simply removes the data from the table. However, if you look at the underlying functionality it does the exact same thing as a drop and create table. The fact that identity columns are also reset makes me believe that it should be defined as a DDL

March 31, 2014 10:35 AM
 

Dejan Sarka said:

Koen, thank you for the comment. However, it does not convince me:-) Security is arbitrary selected. For example, in order to use some BULK INSERT options, you need ALTER TABLE permissions (http://technet.microsoft.com/en-us/library/ms188365.aspx). Does this make BULK INSERT a DDL statement?

March 31, 2014 10:55 AM
 

Dejan Sarka said:

Brian, this is my point - it is a DML because logically, it does a DML operation. However, yes, because of many things it can be treated as a DDL statement as well. My preference is DML, because this is the main idea of the Relational Model  - to work on the logical level and don't care what the underlying system is doing on the physical level.

March 31, 2014 10:57 AM
 

Ante Sabljo said:

Can you consider TRUNCATE table as DML if table doesnt have any data inside? It will not remove any data, but it will change database structure. Analogy is simple, you have an axe, and you have tree full of apples. if you chop off the tree, consequently you lost your apples, but your main intention was to clear out the tree from your yard. so i would say, if you truncate empty table it is DDL, otherwise it must be DML also, same as delete.

April 3, 2014 9:09 AM

Leave a Comment

(required) 
(required) 
Submit

About Dejan Sarka

Dejan Sarka, MCT and SQL Server MVP, is an independent consultant, trainer, and developer focusing on database & business intelligence applications. His specialties are advanced topics like data modeling, data mining, and data quality. On these toughest topics, he works and researches together with SolidQ and The Data Quality Institute. He is the founder of the Slovenian SQL Server and .NET Users Group. Dejan Sarka is the main author or coauthor of eleven books about databases and SQL Server, with more to come. Dejan Sarka also developed and is developing many courses and seminars for SolidQ, Microsoft and Pluralsight. He is a regular speaker at many conferences worldwide for more than 15 years, including conferences like Microsoft TechEd, PASS Summit and others.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement