Several months ago, I was looking at a question posted on ask.sqlservercentral.com. I discovered an answer to a question regarding how to move a large amount of data around quickly.  The solution was related to an aspect of table partitioning that I was not aware of.  If you aren’t familiar with partitions in SQL Server, you can “divide” up a table into different segments or partitions.  Often this is found in large tables (think millions/billions of rows) in order to quickly and efficiently move data around.  Moving partitions around is a meta-data operation which is what makes it so efficient.

From Books Online (BOL):

The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. All partitions of a single index or table must reside in the same database. The table or index is treated as a single logical entity when queries or updates are performed on the data

In SQL Server 2012 and later, you can have up to 15,000 partitions per partitioned table or index as long as you were running the x64 bit version of SQL Server.  If you happen to be running the x32 bit version of SQL Server, you are limited to 1,000 partitions.

Note: if you are still running a x32 bit version of SQL Server, you really need to move to x64 bit.  You’ll do yourself and your organization a huge favor if you do.  

Single Partition SWITCH

In a nutshell, you can use the SWITCH function to quickly move a table, which is a single partition, and all of its data to a new table or schema.

You can see partitions by doing a SELECT against sys.partitions.  Using AdventureWorks2014, we can see below that the Person.Address table has a clustered index (Index_id = 1) and 4 non-clustered indexes on it.  Every index has at least one partition, which is partition_number of one.  If any object has multiple partitions, you’d see it there with a corresponding “partition_number” that is greater than one.

Of course as with anything, there are limitations. The list below is not inclusive, so be sure to read the documentation thoroughly.

• Both tables must exist before the SWITCH operation
• The target partition must be empty
• Tables must have the same column structure (including nullability) and order
• The index structure(s) must be identical between the source and target tables
• Any foreign key constraints must be identical and cannot be marked is not trusted

Let’s try out a demo!

The Fun Stuff

For simplicity, I will:

1. Create a simple table and load 1000 records into it.
2. Create a secondary empty table that is identical in structure to the first table.
   1. This table will be in a different schema, the Archive schema
3. Use the SWITCH command to switch the partition from the first table into the second table.

-- create the source table
CREATE TABLE dbo.Switcharoo
( id INT);
GO
CREATE TABLE archive.Switcharoo
( id INT);
GO

Now that the table structures are created, I’ll load the source table, dbo.Switcharoo with 1 million records.  I’m using a numbers table courtesy of this blog post, slightly modified for the tables that I’m using.  If you are not familiar with a numbers table, it is a very quick and efficient way to load a large amount of numbers.  Using a numbers table, I was able to insert 1 million records into the source table in less than 3 seconds.  You’re milage may vary depending on your hardware, but it will be faster than an iterative based solution.

;WITH
 L0 AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows
 L1 AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows
 L2 AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows
 L3 AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
 L4 AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows
 L5 AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 rows
 Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)
 
 INSERT INTO dbo.Switcharoo (id) SELECT TOP 1000000 N FROM Nums ORDER BY N;

We can see below that the source table now has rows. We can also see that the target table, archive.Switcharoo has zero rows.

Now that we have a good amount of data to switch, we can actually switch the partition from dbo.Switcharoo into archive.Switcharoo.   This is completed with a simple ALTER TABLE command.

ALTER TABLE dbo.Switcharoo SWITCH TO archive.Switcharoo

Because the switch is just metadata, it’s very quick.  Less than a second to move all 1 million records from one table to the other.  As you can see below, now the Archive.Switcharoo table has 1 million records and the dbo.Switcharoo table has zero.

Summary

Each individual table has at least one partition.  If you have to move a large amount of data around even with a single partition, switching them might be a quick, easy, and efficient solution.  A single line of code might just save you a lot of time and effort.  Make sure to read the documentation thoroughly.

If you want to learn more about partitioning (with multiple partitions), Cathrine Wilhelmsen (B|T) has a good series on partitioning. You can read it here.

Enjoy!

© 2018, John Morehouse. All rights reserved.