ALTER TABLE ... SWITCHing from regular table to partitioned table fails

The code below does the following:

• Creates a database play_partition in C:\TEMP

• Creates two identical partitioned tables play_table and archive_play_table

• Switches play_table partition 1 to archive_play_table partition 1

• Creates a new unpartitioned table temp_table with the same structure as play_table on the same filegroup as play_table partition 2

• Switches play_table_partition 2 to temp_table

-
Tries to switch temp_table back to play_table partition 2 and fails with

Msg 4982, Level 16, State 1, Line 64
ALTER TABLE SWITCH statement failed. Check constraints of source table
'play_partition.dbo.temp_table' allow values that are not allowed by
range defined by partition 2 on target table
'play_partition.dbo.play_table'.

Why does it fail?

I am using SQL Server 2014 (Enterprise Edition Trial).

Regards,

Colin Daley

http://www.colindaley.com/translator

```
/ Playing with partitioned tables /

USE master;
GO

DROP DATABASE play_partition;
GO

CREATE DATABASE play_partition
ON PRIMARY(
NAME = play_partition
, FILENAME = 'C:\TEMP\play_partition.mdf')
,FILEGROUP play_fg1(
NAME = play_fg1
,FILENAME = 'C:\TEMP\play_fg1f1.ndf')
,FILEGROUP play_fg2(
NAME = play_fg2f1
,FILENAME = 'C:\TEMP\play_fg2f1.ndf');
GO

USE play_partition;

CREATE PARTITION FUNCTION play_range(INT)
AS RANGE LEFT FOR VALUES(3);

-- Partition scheme
CREATE PARTITION SCHEME play_scheme
AS PARTITION play_range TO (play_fg1, play_fg2);

-- Partitioned tables
CREATE TABLE dbo.play_table(
c1 INT NOT NULL CONSTRAINT PK_play_table_c1 PRIMARY KEY CLUSTERED
)
ON play_scheme(c1);

CREATE TABLE dbo.archive_play_table(
c1 INT NOT NULL CONSTRAINT PK_archive_play_table_c1 PRIMARY KEY CLUSTERED
)
ON play_scheme(c1);

-- partition 1 = {1, 2, 3}, partiion 2 = {4, 5, 6}
INSERT INTO dbo.play_table(c1) VALUES (1), (2), (3), (4), (5), (6);

-- move partition 1 from play_table to archive play_table
ALTER TABLE dbo.play_tabl

When you are working with partition switching, SQL Server will need to verify that the source table/partition boundaries can fit in the destination table/partition boundaries. In other words, you're trying to switch data from dbo.temp_table to dbo.play_table's partition 2. Think of it like this, the data for the c1 in dbo.temp_table is constrained only by the data type (int), so you can have values ranging from -2,147,483,648 to 2,147,483,647. But conversely, you're destination (dbo.play_table partition 2) has a range from 4 to 2,147,483,647.

Your data does not violate this, but it is the metadata that can't allow this. You could just as easily insert the value -10 into dbo.temp_table. The partition switching would fail the same way and make more sense, as -10 does not fit in dbo.play_table's 2nd partition boundaries.

If you wanted to make this code work, you'd need to explicitly tell SQL Server that dbo.temp_table will never have any data that won't fit in dbo.play_table's 2nd partition. You could do this with a check constraint:

/******************************************************************************
    your code omitted for brevity
******************************************************************************/

-- move contents of play_table to temp_table, which is not partitioned
-- but is in the same filegroup
ALTER TABLE dbo.play_table
    SWITCH PARTITION 2 TO temp_table;
PRINT 'Switched from partitioned table to non-partitioned table';

/******************************************************************************
    added check constraint so that data can fit in the destination partition
******************************************************************************/
alter table dbo.temp_table
add constraint CK_TempTable_C1 check (c1 >= 4);
go
/******************************************************************************
    end of added code
******************************************************************************/

-- move data back to partitioned play_table from unpartitioned temp_table
-- this will no longer FAIL
ALTER TABLE dbo.temp_table
    SWITCH TO play_table partition 2;
PRINT 'Switched from non-partitioned table to partitioned table';

/******************************************************************************
    your code omitted for brevity
******************************************************************************/

That above sample addition to your code makes this a working solution. Now SQL Server knows that the data in dbo.temp_table can fit in partition 2 of dbo.play_table because of the added check constraint to dbo.temp_table.

StackExchange Database Administrators Q#69930, answer score: 12