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

ALTER TABLE SWITCH [PARTITION] – DDL or DML?

My previous blog post “Truncate Table – DDL or DML Statement?” got quite a few comments. Now I am continuing with a similar discussion: is the ALTER TABLE SWITCH [PARTITION] DDL (Data Definition Language) or DML (Data Manipulation Language) statement? At least it is clear that this is not a Data Control Language (DCL) statement.

Again, I can find a lot of arguments why this would be a DDL statement. First of all, we all know the classical categorization of the statements:

· DDL includes CREATE, ALTER, and DROP statements

· DCL includes GRANT, REVOKE, and DENY statements

· DML includes SELECT, INSERT, UPDATE, DELETE, and MERGE statements.

SQL Server changes system pages when you use this statement. In addition, you need elevated permissions to use the statement. Clearly, from the syntax perspective, it is a DDL statement.

However, logically, you just move the data from one table or partition to another table or partition. You do not change the schema at all. Therefore, semantically, this is a DML statement. And again, in my opinion, the logical perspective is the most important here, because this is the main point of the Relational Model: work with it from the logical perspective, and leave the physical execution to the underlying system.

Let me show how this statement works. I start with the clean-up code, if some of the objects I am going to create in the tempdb system database already exist.

image

Next, let me create the partition function and the partition scheme, create a demo table dbo.FactInternetSales and populate it with the data from the dbo.FactInternetSales from the AdventureWorksDW2012 demo database. I will also create two additional tables, one for the new data load, and one for the data from the oldest partition of the dbo.FactInternetSales table.

image

Note that the table for the new data includes a check constraints that guarantees that all of the data can be switched to a single partition of the partitioned table. I am loading the dbo.FactInternetSalesNew table with the last data you can find in the demo database, internet sales for year 2008. Let me check the data in all three tables, and also all partitions of the partitioned table.

image

If you check the results, you can see that there is data in three partitions of the partitioned table, and in the table for the new data. Next step is to switch the data from the new data table to a single partition of the partitioned table.

Can I do the same thing with a single DML statement? The TRUNCATE TABLE works logically similarly to the DELETE statement without the WHERE clause if a table is without a trigger and without the identity property. Does something similar exist for the ALTER TABLE SWITCH [PARTITION] statement? The answer is, of course, yes. You can use the composable DML statements with the OUTPUT clause. With the next statement, I am moving all of the data from the oldest partition of the partitioned table to the table created for the old data.

image

Let’s check where the data is now, and if schema has anyhow changed.

image

If you execute the statements, you can clearly see that the effect of the last composable DML statement was completely the same as the effect of the ALTER TABLE SWITCH [PARTITION] statement. The schema did not change a bit. Therefore, the ALTER TABLE SWITCH [PARTITION] is clearly a DML statement.

Published Sunday, April 06, 2014 1:10 PM 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

 

Mark Stacey said:

Interesting outlook.

In my view, clearly DDL: physically, no data is actually moved during a partition switch. It is only metadata that is saying "hey this name points to this data over here". So, just like editing a view to UNION in another table (poor man's partitioning), it's clearly DDL

April 6, 2014 8:23 AM
 

Dejan Sarka said:

Mark, as I said, by the syntax, it is DDL. But you missed the main point: physical operation is not what defines the Relational Model, it is the logical action. So if there would exist only composable DML syntax and MS would not explain the physical action, this would be a DML for you?

April 6, 2014 2:11 PM
 

Rainer Unwin said:

As Mark said, interesting outlook however in my view logically you are changing the name or reference to the data in the partition in most cases. You are not moving data (Hopefully). Exec sp_rename 'do.table1', 'dbo.table2'; is that DDL or DML? To me that is very similar to a partition switch without data movement hence in this case the physical operation also defines the logical operation. If you physically move the data with the switch (To another disk) then DML if there is no data movement I would say that it's DDL as you're just renaming the partition. Oh the joys, one command two operations.

I would say it can be both but used right should be DDL in most cases.

April 9, 2014 2:35 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