T-SQL Tuesday #131 – Star Trek Candy and SWITCH TO

This month’s #tsql2sday came to us from Rob Volk (b|t), who asks us to explain databases using an analogy, as if explaining to a 5 year old. I’m actually a big fan of The Feynman Technique (aka ELI5), so I really wanted to participate. But to be honest, I nearly missed out this time simply because I couldn’t think of an idea this whole week.

On the very last day, when the posts already started rolling out by all the bloggers, I’ve read a few, and only then the muse finally hit me. I kid you not, the time is literally 23:59 here in Israel as I hit the publish button!

So anyways, the idea I had was for the ALTER TABLE..SWITCH TO command in SQL Server.

The SWITCH syntax is actually a very cool feature of SQL Server, often mistakenly thought to be exclusive to partitioned tables, but it’s not! You can use this even for non-partitioned tables, on any version and any edition of SQL Server! Even Express!

So what is the ALTER TABLE SWITCH TO command? Well, I’m glad you asked, because I’ll explain using an analogy 😉.

The Data

Imagine that every table is a collection of… Let’s say… Candy. There’s all kinds of candy: colorful candy, chocolate candy, sweet candy, sour candy, big candy, small candy, square candy, round candy… These different candy are our “data” with their different attributes. But if we want to store this candy somewhere, we’re gonna need some kind of a container. Let’s say… A large bowl.

Sometimes, we’d want to take some candy and move it from one bowl to another bowl. We could move the candy one by one by picking it up from one bowl and putting it into the other bowl. Or maybe we could take a handful or a spoonful of candy at a time and move it to another bowl. Sometimes, we’d want to move ALL the candy to another bowl.

That would be the equivalent of an INSERT..SELECT command. For example:

INSERT INTO dbo.TheNewBowl
SELECT * FROM dbo.TheOldBowl

(I mean, okay, it would actually be more like the DELETE..OUTPUT..INTO.. command, but we’re here to keep things simple, alright?)

However, this kind of operation could take a very long time if you have lots of candy. What if you need to move an entire bowl of candy and you need it to be as fast as possible?

The Final Frontier

Luckily, it just so happens that our analogy-candy exists in the futuristic universe of Star Trek, where instant teleportation technology also exists.

Yes, you guessed it… We’re going to beam up those candy from one bowl to another bowl!

This instant teleportation technology is exactly what the SWITCH TO command feels like!
Its syntax, in its simplest form, looks like this:

ALTER TABLE dbo.TheOldBowl SWITCH TO dbo.TheNewBowl

Zzzzap! All the candy instantly disappear from the old bowl and reappear inside the new bowl!

Now, we end up with TheOldBowl being completely empty, while TheNewBowl‘s contents are exactly what was, until a moment ago, in the old bowl!

Behind The Curtain

The reason why this capability is even possible, is because starting with SQL Server 2005, ALL tables in SQL Server are actually partitioned. They just don’t necessarily have more than one partition. But the crux of the matter is that these partitions are the real containers of data, not the table itself.

All table partitions can be queried from the system table sys.partitions. Notice that its primary key is “partition_id“, but the table identifier is “object_id“.

And so, all one needs to do is change the “owner” of a partition from table 1 to table 2 (not unlike updating a foreign key column, i.e. object_id, to a different value), and the result would look like the instant teleportation of data! In truth, the data never moved. Only a “pointer” was changed from table 1 to table 2, and that is what makes this operation nearly instantaneous.

Okay so what’s the catch?

Yes, there is in fact a catch. Multiple catches, actually. In order to be able to use the SWITCH TO syntax, there are all kinds of prerequisite conditions that must be met. For example:

• All the column names, order, data types, and nullability must be identical between the two tables.
• The destination table (partition) must be empty.
• The source and destination, and their corresponding indexes, must reside on the same filegroup.
• The source table must not be referenced by a foreign key.
• All the indexes, check constraints, and foreign keys which are in the destination table must exist in the source table as well (not necessarily the other way around). Their names don’t have to be identical, though.

There are many more restrictions, all of which can be found in the ALTER TABLE docs page, or in this classic MSDN article. But these restrictions all make sense, considering the underlying mechanic of the SWITCH TO command.

Why would I need this?

Here are a few ideas for use cases where the ALTER TABLE SWITCH TO command could be useful:

• Partition sliding window (this one should be obvious if you’re already familiar with partitioning)
• Removing the IDENTITY property from an existing column.
• Adding the IDENTITY property to an existing column (this one may require using DBCC CHECKIDENT with RESEED to re-align the IDENTITY to reflect the new data).
• Moving the majority of data from one table to another. For example: You need to move 95% of a table to an archiving table, but you want to leave the remaining 5%. So you SWITCH the whole table to an identically structured archiving table, and then INSERT..SELECT the remaining 5% back to the source table, and delete them from the archiving table.

Got a few example use cases of your own? Share them in the comments below!