Table Partitioning in SQL Server - Partition Switching
Inserts, updates and deletes on large tables can be very slow and expensive, cause locking and blocking, and even fill up the transaction log. One of the main benefits of table partitioning is that you can speed up loading and archiving of data by using partition switching.
Partition switching moves entire partitions between tables almost instantly. It is extremely fast because it is a metadata-only operation that updates the location of the data, no data is physically moved. New data can be loaded to separate tables and then switched in, old data can be switched out to separate tables and then archived or purged. All data preparation and manipulation can be done in separate tables without affecting the partitioned table.
Partition Switching Requirements
There are always two tables involved in partition switching. Data is switched from a source table to a target table. The target table (or target partition) must always be empty.
(The first time I heard about partition switching, I thought it meant “partition swapping”. I thought it was possible to swap two partitions that both contained data. This is currently not possible, but I hope it will change in a future SQL Server version.)
Partition switching is easy - as long as the source and target tables meet all the requirements 😊 There are many requirements, but the most important to remember are:
- The source and target tables (or partitions) must have identical columns, indexes and use the same partition column
- The source and target tables (or partitions) must exist on the same filegroup
- The target table (or partition) must be empty
If all the requirements are not met, SQL Server is happy to tell you exactly what went wrong and provides detailed and informative error messages. Some of the most common examples are listed near the end of this blog post.
Partition Switching Examples
Partitions are switched by using the ALTER TABLE SWITCH statement. You ALTER the source table (or partition) and SWITCH to the target table (or partition). There are four ways to use the ALTER TABLE SWITCH statement:
- Non-partitioned table to another non-partitioned table
- Non-partitioned table to a partition in a partitioned table (Load data by switching in)
- Partition in a partitioned table to a non-partitioned table (Archive data by switching out)
- Partition in a partitioned table to a partition in another partitioned table
The following examples use code from the previous Table Partitioning Basics blog post. It is important to notice that these examples are meant to demonstrate the different ways of switching partitions, they do not create any indexes and they map all partitions to the [PRIMARY] filegroup. These examples are not meant to be used in real-world projects.
1. Switch from Non-Partitioned to Non-Partitioned
The first way to use the ALTER TABLE SWITCH statement is to switch all the data from a non-partitioned table to an empty non-partitioned table:
ALTER TABLE Source SWITCH TO Target
Before switch:
After switch:
This is probably not used a lot, but it is a great way to start learning the ALTER TABLE SWITCH statement without having to create partition functions and partition schemes:
-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesSource')
DROP TABLE SalesSource;
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesTarget')
DROP TABLE SalesTarget;
-- Create the Non-Partitioned Source Table (Heap) on the [PRIMARY] filegroup
CREATE TABLE SalesSource (
SalesDate DATE,
Quantity INT
) ON [PRIMARY];
-- Insert test data
INSERT INTO SalesSource(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2021-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2021-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;
-- Create the Non-Partitioned Target Table (Heap) on the [PRIMARY] filegroup
CREATE TABLE SalesTarget (
SalesDate DATE,
Quantity INT
) ON [PRIMARY];
-- Verify row count before switch
SELECT COUNT(*) FROM SalesSource; -- 1461000 rows
SELECT COUNT(*) FROM SalesTarget; -- 0 rows
-- Turn on statistics
SET STATISTICS TIME ON;
-- Is it really that fast...?
ALTER TABLE SalesSource SWITCH TO SalesTarget;
-- YEP! SUPER FAST!
-- Turn off statistics
SET STATISTICS TIME OFF;
-- Verify row count after switch
SELECT COUNT(*) FROM SalesSource; -- 0 rows
SELECT COUNT(*) FROM SalesTarget; -- 1461000 rows
-- If we try to switch again we will get an error:
ALTER TABLE SalesSource SWITCH TO SalesTarget;
-- Msg 4905, ALTER TABLE SWITCH statement failed. The target table 'SalesTarget' must be empty.
-- But if we try to switch back to the now empty Source table, it works:
ALTER TABLE SalesTarget SWITCH TO SalesSource;
-- (...STILL SUPER FAST!)
2. Load data by switching in: Switch from Non-Partitioned to Partition
The second way to use the ALTER TABLE SWITCH statement is to switch all the data from a non-partitioned table to an empty specified partition in a partitioned table:
ALTER TABLE Source SWITCH TO Target PARTITION 1
Before switch:
After switch:
This is usually referred to as switching in to load data into partitioned tables. The non-partitioned table must specify WITH CHECK constraints to ensure that the data can be switched into the specified partition:
-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesSource')
DROP TABLE SalesSource;
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesTarget')
DROP TABLE SalesTarget;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
DROP PARTITION FUNCTION pfSales;
-- Create the Partition Function
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE RIGHT FOR VALUES
('2022-01-01', '2023-01-01', '2024-01-01');
-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales
ALL TO ([Primary]);
-- Create the Non-Partitioned Source Table (Heap) on the [PRIMARY] filegroup
CREATE TABLE SalesSource (
SalesDate DATE,
Quantity INT
) ON [PRIMARY];
-- Insert test data
INSERT INTO SalesSource(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2021-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2021-01-01','2022-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;
-- Create the Partitioned Target Table (Heap) on the Partition Scheme
CREATE TABLE SalesTarget (
SalesDate DATE,
Quantity INT
) ON psSales(SalesDate);
-- Insert test data
INSERT INTO SalesTarget(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2022-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2022-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;
-- Verify row count before switch
SELECT COUNT(*) FROM SalesSource; -- 366000 rows
SELECT
pstats.partition_number AS PartitionNumber
,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesTarget')
ORDER BY PartitionNumber; -- 0 rows in Partition 1, 365000 rows in Partitions 2-4
-- Turn on statistics
SET STATISTICS TIME ON;
-- Is it really that fast...?
ALTER TABLE SalesSource SWITCH TO SalesTarget PARTITION 1;
-- NOPE! We get an error:
-- Msg 4982, ALTER TABLE SWITCH statement failed. Check constraints of source table 'SalesSource'
-- allow values that are not allowed by range defined by partition 1 on target table 'Sales'.
-- Add constraints to the source table to ensure it only contains data with values
-- that are allowed in partition 1 on the target table
ALTER TABLE SalesSource
WITH CHECK ADD CONSTRAINT ckMinSalesDate
CHECK (SalesDate IS NOT NULL AND SalesDate >= '2021-01-01');
ALTER TABLE SalesSource
WITH CHECK ADD CONSTRAINT ckMaxSalesDate
CHECK (SalesDate IS NOT NULL AND SalesDate < '2022-01-01');
-- Try again. Is it really that fast...?
ALTER TABLE SalesSource SWITCH TO SalesTarget PARTITION 1;
-- YEP! SUPER FAST!
-- Turn off statistics
SET STATISTICS TIME OFF;
-- Verify row count after switch
SELECT COUNT(*) FROM SalesSource; -- 0 rows
SELECT
pstats.partition_number AS PartitionNumber
,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesTarget')
ORDER BY PartitionNumber; -- 366000 rows in Partition 1, 365000 rows in Partitions 2-4
3. Archive data by switching out: Switch from Partition to Non-Partitioned
The third way to use the ALTER TABLE SWITCH statement is to switch all the data from a specified partition in a partitioned table to an empty non-partitioned table:
ALTER TABLE Source SWITCH PARTITION 1 TO Target
Before switch:
After switch:
This is usually referred to as switching out to archive data from partitioned tables:
-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesSource')
DROP TABLE SalesSource;
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesTarget')
DROP TABLE SalesTarget;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
DROP PARTITION FUNCTION pfSales;
-- Create the Partition Function
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE RIGHT FOR VALUES
('2022-01-01', '2023-01-01', '2024-01-01');
-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales
ALL TO ([Primary]);
-- Create the Partitioned Source Table (Heap) on the Partition Scheme
CREATE TABLE SalesSource (
SalesDate DATE,
Quantity INT
) ON psSales(SalesDate);
-- Insert test data
INSERT INTO SalesSource(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2021-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2021-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;
-- Create the Non-Partitioned Target Table (Heap) on the [PRIMARY] filegroup
CREATE TABLE SalesTarget (
SalesDate DATE,
Quantity INT
) ON [PRIMARY];
-- Verify row count before switch
SELECT
pstats.partition_number AS PartitionNumber
,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('Sales')
ORDER BY PartitionNumber; -- 366000 rows in Partition 1, 365000 rows in Partitions 2-4
SELECT COUNT(*) FROM SalesTarget; -- 0 rows
-- Turn on statistics
SET STATISTICS TIME ON;
-- Is it really that fast...?
ALTER TABLE SalesSource SWITCH PARTITION 1 TO SalesTarget;
-- YEP! SUPER FAST!
-- Turn off statistics
SET STATISTICS TIME OFF;
-- Verify row count after switch
SELECT
pstats.partition_number AS PartitionNumber
,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesSource')
ORDER BY PartitionNumber; -- 0 rows in Partition 1, 365000 rows in Partitions 2-4
SELECT COUNT(*) FROM SalesTarget; -- 366000 rows
4. Switch from Partition to Partition
The fourth way to use the ALTER TABLE SWITCH statement is to switch all the data from a specified partition in a partitioned table to an empty specified partition in another partitioned table:
ALTER TABLE Source SWITCH PARTITION 1 TO Target PARTITION 1
Before switch:
After switch:
This can be used when data needs to be archived in another partitioned table:
-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesSource')
DROP TABLE SalesSource;
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesTarget')
DROP TABLE SalesTarget;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
DROP PARTITION FUNCTION pfSales;
-- Create the Partition Function
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE RIGHT FOR VALUES
('2022-01-01', '2023-01-01', '2024-01-01');
-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales
ALL TO ([Primary]);
-- Create the Partitioned Source Table (Heap) on the Partition Scheme
CREATE TABLE SalesSource (
SalesDate DATE,
Quantity INT
) ON psSales(SalesDate);
-- Insert test data
INSERT INTO SalesSource(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2021-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2021-01-01','2022-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;
-- Create the Partitioned Target Table (Heap) on the Partition Scheme
CREATE TABLE SalesTarget (
SalesDate DATE,
Quantity INT
) ON psSales(SalesDate);
-- Insert test data
INSERT INTO SalesTarget(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2022-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2022-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;
-- Verify row count before switch
SELECT
pstats.partition_number AS PartitionNumber
,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesSource')
ORDER BY PartitionNumber; -- 366000 rows in Partition 1, 0 rows in Partitions 2-4
SELECT
pstats.partition_number AS PartitionNumber
,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesTarget')
ORDER BY PartitionNumber; -- 0 rows in Partition 1, 365000 rows in Partitions 2-4
-- Turn on statistics
SET STATISTICS TIME ON;
-- Is it really that fast...?
ALTER TABLE SalesSource SWITCH PARTITION 1 TO SalesTarget PARTITION 1;
-- YEP! SUPER FAST!
-- Turn off statistics
SET STATISTICS TIME OFF;
-- Verify row count after switch
SELECT
pstats.partition_number AS PartitionNumber
,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesSource')
ORDER BY PartitionNumber; -- 0 rows in Partition 1-4
SELECT
pstats.partition_number AS PartitionNumber
,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesTarget')
ORDER BY PartitionNumber; -- 366000 rows in Partition 1, 365000 rows in Partitions 2-4
Error messages
SQL Server provides detailed and informative error messages if not all requirements are met before switching partitions. You can see all messages related to ALTER TABLE SWITCH by executing the following query, it is also quite a handy requirements checklist:
SELECT message_id, text
FROM sys.messages
WHERE language_id = 1033
AND text LIKE '%ALTER TABLE SWITCH%';
Summary
Partition switching moves entire partitions between tables almost instantly. New data can be loaded to separate tables and then switched in, old data can be switched out to separate tables and then archived or purged. There are many requirements for switching partitions. It is important to understand and test how partition switching works with filegroups, indexes and constraints.
This post is the second in a series of Table Partitioning in SQL Server blog posts. It covers the basics of partition switching. Future blog posts in this series will build upon this information and these examples to explain other and more advanced concepts.
About the Author
Cathrine Wilhelmsen is a Microsoft Data Platform MVP, international speaker, author, blogger, organizer, and chronic volunteer. She loves data and coding, as well as teaching and sharing knowledge - oh, and sci-fi, gaming, coffee and chocolate 🤓