Switch-Out a Partition in SQL Server (T-SQL)

Posted on by Ian

In SQL Server, partition switching allows you to load large amounts of data in or out of a table very quickly. This saves you from having to run delete or insert statements, and can be very useful when working with large data sets.

You can use the ALTER TABLE statement to switch a partition in or out of a table.

To switch a partition out of a table, the code goes like this:

ALTER TABLE Table1
SWITCH PARTITION x TO Table2

This switches partition x from Table1 to Table2 (where x is the partition number).

Example

The Setup

Before we start switching out, we’ll create the basic setup. This will consist of two tables. One will be the partitioned source table, the other will be the destination table. We’ll also create four filegroups – one for each partition. 

-- Create filegroups
ALTER DATABASE Test ADD FILEGROUP OrdersLatestFg1;
GO

ALTER DATABASE Test ADD FILE (  
    NAME = OrdersLatestFg1dat,  
    FILENAME = '/var/opt/mssql/data/OrdersLatestFg1dat.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB
    )  
TO FILEGROUP OrdersLatestFg1;
GO

ALTER DATABASE Test ADD FILEGROUP OrdersLatestFg2;
GO

ALTER DATABASE Test ADD FILE (  
    NAME = OrdersLatestFg2dat,  
    FILENAME = '/var/opt/mssql/data/OrdersLatestFg2dat.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB
    )  
TO FILEGROUP OrdersLatestFg2;
GO
ALTER DATABASE Test ADD FILEGROUP OrdersLatestFg3;
GO

ALTER DATABASE Test ADD FILE (  
    NAME = OrdersLatestFg3dat,  
    FILENAME = '/var/opt/mssql/data/OrdersLatestFg3dat.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB
    )  
TO FILEGROUP OrdersLatestFg3;
GO
ALTER DATABASE Test ADD FILEGROUP OrdersLatestFg4;
GO

ALTER DATABASE Test ADD FILE (  
    NAME = OrdersLatestFg4dat,  
    FILENAME = '/var/opt/mssql/data/OrdersLatestFg4dat.ndf',  
    SIZE = 5MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5MB
    )  
TO FILEGROUP OrdersLatestFg4;
GO

-- Create a partition function that will result in four partitions  
CREATE PARTITION FUNCTION OrdersLatestPartitionFunction (date)  
    AS RANGE RIGHT FOR VALUES (
        '20200201', 
        '20200301',
        '20200401'
    );
GO

-- Create a partition scheme that maps the partitions to the filegroups
CREATE PARTITION SCHEME OrdersLatestPartitionScheme
    AS PARTITION OrdersLatestPartitionFunction  
    TO (
        OrdersLatestFg1,
        OrdersLatestFg2,
        OrdersLatestFg3,
        OrdersLatestFg4
        );  
GO

-- Create a partitioned table called OrdersLatest that uses the OrderDate column as the partitioning column
CREATE TABLE OrdersLatest (
    OrderDate date NOT NULL,
    OrderId int IDENTITY NOT NULL,
    OrderDesc varchar(255) NOT NULL,
    CONSTRAINT PKOrdersLatest PRIMARY KEY CLUSTERED(OrderDate, OrderId)
    )  
    ON OrdersLatestPartitionScheme(OrderDate);  
GO

-- Insert data into the OrdersLatest table. 
-- This will end up in partition 3, which is the partition we will switch out to the OrdersMarch table.
INSERT INTO OrdersLatest(OrderDate, OrderDesc) VALUES
    ('20200302', 'Cat food'),
    ('20200315', 'Water bowl'),
    ('20200318', 'Saddle for camel'),
    ('20200321', 'Dog biscuits'),
    ('20200328', 'Bigfoot shoes');
GO

-- Create a table that contains the data that we will be switching out to.  
-- Note that the filegroup matches the filegroup of the partition that we will switch out of.
CREATE TABLE OrdersMarch (
    OrderDate date NOT NULL,
    OrderId int IDENTITY NOT NULL,
    OrderDesc varchar(255) NOT NULL,
    CONSTRAINT PKOrdersMarch PRIMARY KEY CLUSTERED(OrderDate, OrderId)
    )
    ON OrdersLatestFg3;
GO

-- Check how many rows are in each table
SELECT COUNT(*) AS OrdersLatest
FROM OrdersLatest;

SELECT COUNT(*) AS OrdersMarch 
FROM OrdersMarch;

Result:

+----------------+
| OrdersLatest   |
|----------------|
| 5              |
+----------------+

+---------------+
| OrdersMarch   |
|---------------|
| 0             |
+---------------+

So as it currently stands, we have five rows in the OrdersLatest table, which is our partitioned table.

All five rows should be in partition 3. Let’s check that.

SELECT 
    p.partition_number AS [Partition], 
    fg.name AS [Filegroup], 
    p.Rows
FROM sys.partitions p
    INNER JOIN sys.allocation_units au
    ON au.container_id = p.hobt_id
    INNER JOIN sys.filegroups fg
    ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('OrdersLatest')
ORDER BY [Partition];

Result:

+-------------+-----------------+--------+
| Partition   | Filegroup       | Rows   |
|-------------+-----------------+--------|
| 1           | OrdersLatestFg1 | 0      |
| 2           | OrdersLatestFg2 | 0      |
| 3           | OrdersLatestFg3 | 5      |
| 4           | OrdersLatestFg4 | 0      |
+-------------+-----------------+--------+

Yes, so we can see that all five rows are in partition 3. We can also see that partition 3 is mapped to the OrdersLatestFg3 filegroup. For our “switch out” to be successful, we need to ensure that our destination table uses this filegroup. Fortunately, our above code does exactly that. We used ON OrdersLatestFg3 when creating the table in order to specify that the table should be created on that filegroup.

Switch Out

OK, so everything is ready to switch out. Let’s do it.

ALTER TABLE OrdersLatest
SWITCH PARTITION 3 TO OrdersMarch;

Result:

Commands completed successfully.

Excellent. Our switch out worked.

Let’s check the number of rows in each table again.

SELECT COUNT(*) AS OrdersLatest
FROM OrdersLatest;

SELECT COUNT(*) AS OrdersMarch 
FROM OrdersMarch;

Result:

+----------------+
| OrdersLatest   |
|----------------|
| 0              |
+----------------+

+---------------+
| OrdersMarch   |
|---------------|
| 5             |
+---------------+

So we can see that the data has been moved from the OrdersLatest table to OrdersMarch table.

Let’s check the partition info for OrdersLatest.

SELECT 
    p.partition_number AS [Partition], 
    fg.name AS [Filegroup], 
    p.Rows
FROM sys.partitions p
    INNER JOIN sys.allocation_units au
    ON au.container_id = p.hobt_id
    INNER JOIN sys.filegroups fg
    ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('OrdersLatest')
ORDER BY [Partition];

Result:

+-------------+-----------------+--------+
| Partition   | Filegroup       | Rows   |
|-------------+-----------------+--------|
| 1           | OrdersLatestFg1 | 0      |
| 2           | OrdersLatestFg2 | 0      |
| 3           | OrdersLatestFg3 | 0      |
| 4           | OrdersLatestFg4 | 0      |
+-------------+-----------------+--------+

As expected, the OrdersLatestFg3 partition is now empty. This is because it has been switched out.

Let’s check the partition info for the OrdersMarch table.

SELECT 
    p.partition_number AS [Partition], 
    fg.name AS [Filegroup], 
    p.Rows
FROM sys.partitions p
    INNER JOIN sys.allocation_units au
    ON au.container_id = p.hobt_id
    INNER JOIN sys.filegroups fg
    ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('OrdersMarch')
ORDER BY [Partition];

Result:

+-------------+-----------------+--------+
| Partition   | Filegroup       | Rows   |
|-------------+-----------------+--------|
| 1           | OrdersLatestFg3 | 5      |
+-------------+-----------------+--------+

Again as expected, the OrdersMarch table contains five rows. They are stored in partition 1 (the only partition) on the OrdersLatest3 filegroup.

Switching In

See Switch-In a Partition in SQL Server for how to switch-in a partition.