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.
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.
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.
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.
Let’s check where the data is now, and if schema has anyhow changed.
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.