Partition Switching in SQL Server

Continuing with our recent theme of partitioning I wanted to make sure that we looked at one of my personal favourite features – Partition Switching.

Partition Switching is the ability to switch an entire partition of data from one table to another almost instantly. Where it gets interesting is that this is the same operation whether the partition contains 1 record, 100,000 records, or 100,000,000 records.

How does partition switching work?

As we saw earlier in the series we use partition functions to help split our data up into individual segments – or partitions – based on a given threshold. In previous examples we’ve used date-based boundaries which is quite common in real-world use cases.

When our data is stored into a partitioned table you can imagine that each partition is its own smaller version of the same table but the only records which are present in the partition are ones which match the boundary values. They’re like buckets.

Partition switching takes advantage of the fact that each of the partitions is stored individually and that each of them is linked by metadata. If we know that two tables share the same partition scheme then the metadata for those tables will have commonality. By quickly changing the metadata we can therefore change which partition resides in which table.

Going back to the analogy of buckets, lets imagine our tables are lines of numbered buckets on a beach – a line of red buckets and a line of blue buckets. They’re both in a line next to each other so it wouldn’t take a lot of effort to swap red bucket #10 for blue bucket #10. It wouldn’t matter whether the bucket is filled with sand, water or shells, we’d still have two lines of buckets in numerical order.

That’s essentially partition switching, but rather than physically moving anything, our operation is metadata based so it’s very quick and doesn’t require rewriting all of the data into our target table.

Why would you use partition switching?

There are a number of use cases where this can be useful however there are two which come to mind where you may want to move large volumes of data very quickly without impacting performance or causing blocking for extended periods:

• Purging legacy data by removing an old chunk from a table into an archive copy
• Loading large volumes of data (e.g. data warehousing) into a fact table as part of a batch routine

Both of these examples will allow to you have a separate table containing the data where the heavy work can take place split off from the core operational data which you’d want to keep available and performant.

In the case of archiving you can move the data out of the table and then choose if you wanted to purge some of it, compress it, move to a different file group, etc. On the data loading side it would allow ETL type jobs import the data at a relevant cadence before switching it into the reporting data set. Both scenarios would keep the core data available with an instant switch in/out when needed with minimal brief blocking.

So how do we do it?

I’m glad you asked! (you did, didn’t you?)

Below is a script we’ll use to set up our example. This will create our partition function, schema, table, and add 500k records across a few of the partitions:

/* Create the partition function */
CREATE PARTITION FUNCTION MyPartFunc (DATE)
AS RANGE RIGHT
FOR VALUES ('2022-12-01', '2022-12-02', '2022-12-03', '2022-12-04',
	'2022-12-05', '2022-12-06', '2022-12-07', '2022-12-08');

/* Create the partition scheme */
CREATE PARTITION SCHEME MyPartScheme  
AS PARTITION MyPartFunc  
ALL TO ([PRIMARY]);

/* Create the table */
CREATE TABLE SalesData (
	SalesDate DATE,
	SalesQuantity INT,
	SalesValue DECIMAL(9, 2)
) ON MyPartScheme (SalesDate);
GO

/* Populate some data */
INSERT INTO SalesData (SalesDate, SalesQuantity, SalesValue)
VALUES ('2022-12-01', 1, 10.00),
	('2022-12-02', 2, 20.00),
	('2022-12-03', 3, 30.00),
	('2022-12-04', 4, 40.00),
	('2022-12-05', 5, 50.00);
GO 100000

With that in place let’s suppose we want to load another 100,000 records in for 6th December. Depending on how we choose to do that it may cause some blocking on that table so we want to try and use partition switching to avoid that.

We’ll start by creating another table to stage our data which will have the same schema and crucially be based on the same partition scheme:

CREATE TABLE SalesData_Staging (
	SalesDate DATE,
	SalesQuantity INT,
	SalesValue DECIMAL(9, 2)
) ON MyPartScheme (SalesDate);
GO

This is the table where we’ll load our data into and then we’ll switch our partition with the data into our main sales table. Let’s do the legwork and get data loaded into the staging table ready to go:

INSERT INTO SalesData_Staging (SalesDate, SalesQuantity, SalesValue)
VALUES ('2022-12-06', 6, 60.00);
GO 100000

Switcheroo

Before we proceed I’d like to take a look at the data in the tables. To do that we’ll introduce the SQL function $PARTITION which allows us to find which partition a particular value would fall into for a specific function. We’ll simply tell it which function we want to use and pass our value in to have the partition number returned. There’s more detail about the function available in the online documentation.

Let’s now interrogate our two tables to see which partitions are populated in both of them based on the data previously provided:

/* Review partitions in the main table */
SELECT SalesDate, COUNT(1) [Records],
	$PARTITION.MyPartFunc(SalesDate) [PartitionNumber]
FROM SalesData
GROUP BY SalesDate,
	$PARTITION.MyPartFunc(SalesDate)
ORDER BY SalesDate;

/* Review partitions in the staging table */
SELECT SalesDate, COUNT(1) [Records],
	$PARTITION.MyPartFunc(SalesDate) [PartitionNumber]
FROM SalesData_Staging
GROUP BY SalesDate,
	$PARTITION.MyPartFunc(SalesDate)
ORDER BY SalesDate;

That looks great, we’ve got a new partition populated with data ready to be switched in and at this point we haven’t needed to do any work on our main table since we set it up.

So let’s go for it, let’s make the switch.

We’ll make an ALTER TABLE statement indicating which partitions we’d like to switch from and to against the relevant tables. The partitions need to be provided as the partition number so we’ll use our $PARTITION function from before to supply that for the specific date we’re switching in:

ALTER TABLE dbo.SalesData_Staging
SWITCH PARTITION $partition.MyPartFunc('2022-12-06')
TO dbo.SalesData
PARTITION $partition.MyPartFunc('2022-12-06');

As you’ll see this operation is instant as it’s only moving the metadata and the actual data stays in the partition it’s already been loaded into. Let’s see how our partitions look by running the same queries that we did before:

We’ve got our data shuffled across and the blocking on the main table was for all of the instant it took the switch to take place.

Wrap up

Partition switching is a fantastic tool available for partitioned tables. It can help us perform potentially long running or expensive operations near instantly by doing the work on an offline copy of the data and simply switching it where we need to.

As we mentioned in our introduction partitioning can be used to help improve performance and maintenance of our data and this is a great example of where the two come together nicely.