A gotcha when switching partitions

Andrew Pruski, 2018-02-28

When working with partitioning the SWITCH operation has to be my favourite. The ability to move a large amount of data from one table to another as a META DATA ONLY operation is absolutely fantastic.

What’s also cool is that we can switch data into a non-partitioned table. Makes life a bit easier not having to manage two sets of partitions!

However, there is a bit of a gotcha when doing this. Let’s run through a quick demo.

First create a database with a partitioned table: –

CREATE DATABASE [PartitioningDemo]
GO
USE [PartitioningDemo];
GO
CREATE PARTITION FUNCTION PF_PartitionedTable(DATE)
	   AS RANGE RIGHT
    FOR VALUES ('2011-01-01','2012-01-01','2013-01-01',
                '2014-01-01','2015-01-01','2016-01-01',
                '2017-01-01');
GO
CREATE PARTITION SCHEME PS_PartitionedTable
    AS PARTITION PF_PartitionedTable
ALL TO ([PRIMARY]);
GO
CREATE TABLE dbo.PartitionedTable
(ID INT IDENTITY(1,1),
 ColA VARCHAR(10),
 ColB VARCHAR(10),
 CreatedDate DATE)
 ON PS_PartitionedTable(CreatedDate);
GO

For a more in-depth look at what I’m doing, my series on partitioning can be found here

So let’s insert some test data:-

SET NOCOUNT ON;
DECLARE @FromDate date = '2011-01-01';
DECLARE @ToDate date = '2017-01-01';
INSERT INTO dbo.PartitionedTable
SELECT 
    REPLICATE('A',10),
    REPLICATE('B',10),
    DATEADD(DD,FLOOR(RAND()*(DATEDIFF(DD,@FromDate,@ToDate))),@FromDate);
GO 1000

We can check the data and partitions by running: –

SELECT 
	p.partition_number, p.partition_id, fg.name AS [filegroup],
	r.boundary_id, CONVERT(DATE,r.value) AS BoundaryValue, p.rows
FROM 
	sys.tables AS t
INNER JOIN
	sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN
	sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id 
INNER JOIN 
    sys.allocation_units a ON a.container_id = p.hobt_id 
INNER JOIN 
    sys.filegroups fg ON fg.data_space_id = a.data_space_id 
INNER JOIN
	sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
INNER JOIN
	sys.partition_functions AS f ON s.function_id = f.function_id
LEFT OUTER JOIN 
	sys.partition_range_values AS r ON f.function_id = r.function_id 
									AND r.boundary_id = p.partition_number
WHERE 
	i.type <= 1 AND a.type = 1
AND 
	t.name = 'PartitionedTable'
ORDER BY 
	p.partition_number 
		DESC;

Now let’s create the “switch” table: –

USE [PartitioningDemo];
GO
CREATE TABLE dbo.PartitionedTable_Switch
(ID INT IDENTITY(1,1),
 ColA VARCHAR(10),
 ColB VARCHAR(10),
 CreatedDate DATE)
 ON [PRIMARY];
GO

N.B. – Keeping it simple, a non-partitioned table on the PRIMARY filegroup (the same filegroup as all my partitions in the “source” table).

OK, let’s switch one of the partitions to the switch table: –

ALTER TABLE [dbo].PartitionedTable
	SWITCH PARTITION 3
TO [dbo].PartitionedTable_Switch;
GO

All good! Worked exactly as expected ??

But what happens when say, because of some issue, we need to switch that data back?

ALTER TABLE [dbo].PartitionedTable_Switch
    SWITCH 
TO [dbo].PartitionedTable
    PARTITION 3;
GO

Oh no!

Msg 4982, Level 16, State 1, Line 4

ALTER TABLE SWITCH statement failed. Check constraints of source table ‘PartitioningDemo.dbo.PartitionedTable_Switch’

allow values that are not allowed by range defined by partition 3 on target table ‘PartitioningDemo.dbo.PartitionedTable’.

What’s happened??

Well, we’re trying to insert data into a partition that has constraints on it. The partition has a lower boundary of 2012-01-01 and an upper boundary of 2013-01-01. Meaning that no data can go into that partition that has values in the CreatedDate field that isn’t greater than or equal to 2012-01-01 and less than 2013-01-01.

But our switch table doesn’t have these constraints. SQL thinks that there could be data in the switch table that doesn’t fit into the destination partition.

So we need to tell SQL that the data in the switch table will fit into the partition. And we do that by dropping a constraint onto the table: –

ALTER TABLE dbo.PartitionedTable_Switch
		ADD CONSTRAINT CreatedDate_Switch_CHECK CHECK 
			(CreatedDate >= CONVERT(DATE,'2012-01-01') AND CreatedDate < CONVERT(DATE,'2013-01-01')
            AND CreatedDate IS NOT NULL);
GO

N.B. – notice the IS NOT NULL as well ??

And now try the switch again: –

ALTER TABLE [dbo].PartitionedTable_Switch
    SWITCH 
TO [dbo].PartitionedTable
    PARTITION 3;
GO

Woo hoo! We’ve got our data back into our main table. So, you don’t have to partition tables that you want to switch data out to, but just be aware that if you do, you need to be able to switch that data back (just in case).

Thanks for reading!

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads