In SQL Server, you can switch partitions in and out of a partitioned table.
You can do this with the ALTER TABLE statement. Basically, it goes like this:
ALTER TABLE OldTable
SWITCH TO NewTable PARTITION xThis switches the partition for OldTable into partition x of NewTable (where x is the partition number).
Example
Before we start switching in, let’s set up two tables. One (called OrdersOld) will contain the data that we want to “switch in” to the other table (called OrdersNew).
We will partition OrdersNew into four partitions.
-- Create filegroups
ALTER DATABASE Test ADD FILEGROUP OrdersNewFg1;
GO
ALTER DATABASE Test ADD FILE (
NAME = OrdersNewFg1dat,
FILENAME = '/var/opt/mssql/data/OrdersNewFg1dat.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP OrdersNewFg1;
GO
ALTER DATABASE Test ADD FILEGROUP OrdersNewFg2;
GO
ALTER DATABASE Test ADD FILE (
NAME = OrdersNewFg2dat,
FILENAME = '/var/opt/mssql/data/OrdersNewFg2dat.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP OrdersNewFg2;
GO
ALTER DATABASE Test ADD FILEGROUP OrdersNewFg3;
GO
ALTER DATABASE Test ADD FILE (
NAME = OrdersNewFg3dat,
FILENAME = '/var/opt/mssql/data/OrdersNewFg3dat.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP OrdersNewFg3;
GO
ALTER DATABASE Test ADD FILEGROUP OrdersNewFg4;
GO
ALTER DATABASE Test ADD FILE (
NAME = OrdersNewFg4dat,
FILENAME = '/var/opt/mssql/data/OrdersNewFg4dat.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)
TO FILEGROUP OrdersNewFg4;
GO
-- Create a partition function that will result in four partitions
CREATE PARTITION FUNCTION OrdersNewPartitionFunction (date)
AS RANGE RIGHT FOR VALUES (
'20200201',
'20200301',
'20200401'
);
GO
-- Create a partition scheme that maps the partitions to the filegroups
CREATE PARTITION SCHEME OrdersNewPartitionScheme
AS PARTITION OrdersNewPartitionFunction
TO (
OrdersNewFg1,
OrdersNewFg2,
OrdersNewFg3,
OrdersNewFg4
);
GO
-- Create a table that contains the data that we will be switching in.
-- Note that the filegroup matches the filegroup of the partition that we will switch in to.
-- Include CHECK constraint to restrict data to the range specified in the switch-in partition
CREATE TABLE OrdersOld (
OrderDate date NOT NULL,
OrderId int IDENTITY NOT NULL,
OrderDesc varchar(255) NOT NULL,
CONSTRAINT chkDate CHECK (OrderDate >= '20200301' AND OrderDate < '20200401'),
CONSTRAINT PKOrdersOld PRIMARY KEY CLUSTERED(OrderDate, OrderId)
)
ON OrdersNewFg3;
GO
-- Insert data into the OrdersOld table. This is the data we will be switching in to the OrdersNew table.
INSERT INTO OrdersOld(OrderDate, OrderDesc) VALUES
('20200302', 'Cat food'),
('20200315', 'Water bowl'),
('20200318', 'Saddle for camel'),
('20200321', 'Dog biscuits'),
('20200328', 'Bigfoot shoes');
GO
-- Create a partitioned table called OrdersNew that uses the OrderDate column as the partitioning column
CREATE TABLE OrdersNew (
OrderDate date NOT NULL,
OrderId int IDENTITY NOT NULL,
OrderDesc varchar(255) NOT NULL,
CONSTRAINT PKOrdersNew PRIMARY KEY CLUSTERED(OrderDate, OrderId)
)
ON OrdersNewPartitionScheme (OrderDate);
GO
-- Check how many rows are in each table
SELECT COUNT(*) AS OrdersOld
FROM OrdersOld;
SELECT COUNT(*) AS OrdersNew
FROM OrdersNew;Result:
+-------------+ | OrdersOld | |-------------| | 5 | +-------------+ +-------------+ | OrdersNew | |-------------| | 0 | +-------------+
So as it stands right now, OrdersOld contains 5 rows and OrdersNew is empty.
Time to switch in the data.
ALTER TABLE OrdersOld
SWITCH TO OrdersNew PARTITION 3;Result:
Commands completed successfully.
The data has now been successfully switched to partition 3 of the destination table.
Let’s check both tables again.
SELECT COUNT(*) AS OrdersOld
FROM OrdersOld;
SELECT COUNT(*) AS OrdersNew
FROM OrdersNew;Result:
+-------------+ | OrdersOld | |-------------| | 0 | +-------------+ +-------------+ | OrdersNew | |-------------| | 5 | +-------------+
This time OrdersOld is empty and OrdersNew contains 5 rows.
We can also run the following query to check the actual partition that the data is located in.
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('OrdersNew')
ORDER BY [Partition];Result:
+-------------+--------------+--------+ | Partition | Filegroup | Rows | |-------------+--------------+--------| | 1 | OrdersNewFg1 | 0 | | 2 | OrdersNewFg2 | 0 | | 3 | OrdersNewFg3 | 5 | | 4 | OrdersNewFg4 | 0 | +-------------+--------------+--------+
As expected, all 5 rows are allocated to partition 3, in the OrdersNewFg3 filegroup.
Common Errors
Error 4982
In my example above, you’ll notice that I created a CHECK constraint when creating the OrdersOld table.
If you get error message 4982 (ALTER TABLE SWITCH statement failed...), it could be that you didn’t create a CHECK constraint on the source table.
Or it could be that you created a CHECK constraint, but it doesn’t enforce values between the range of the switch-in partition.
You need to ensure that the switch-in values are in fact, within the range defined by the partition, and SQL Server will look for a CHECK constraint on the source table that verifies this.
Error 4939
Another common one is error 4939 (ALTER TABLE SWITCH statement failed...).
If you get this error, it’s probably because you’re trying to switch to a partition that uses a different filegroup to the source table.
One of the requirements of switching partitions is that both the source table or partition, and the target table or partition, must be located in the same filegroup.
To fix this error, make sure the source table uses the same filegroup as the destination partition.
Switching Out
See Switch-Out a Partition in SQL Server for how to switch-out a partition.