Partitioning "Alter Table Switch" Statement Failing

Help!! I can't seem to find information on the error that I'm getting anywhere:

ALTER TABLE SWITCH statement failed. Range defined by partition 1 in table 'DB1.dbo.Table1' is not a subset of range defined by partition 4 in table 'DB1.dbo.Table2'.

Here's some sample code that generates this error

Code Snippet

CREATE PARTITION FUNCTION [Table1Range](int) AS RANGE LEFT FOR VALUES (443, 444, 445)

CREATE PARTITION FUNCTION [Table2Range](int) AS RANGE LEFT FOR VALUES (440, 441, 442, 443)

GO

CREATE PARTITION SCHEME [Table1Scheme] AS PARTITION [Table1Range] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

CREATE PARTITION SCHEME [Table2Scheme] AS PARTITION [Table2Range] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

GO

CREATE TABLE [dbo].[Table1](

[session_id] [int] NOT NULL,

[ProcessLogID] [int] NOT NULL,

CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED

(

[session_id] ASC,

[ProcessLogID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [Table1Scheme]([ProcessLogID])

) ON [Table1Scheme]([ProcessLogID])

CREATE TABLE [dbo].[Table2](

[session_id] [int] NOT NULL,

[ProcessLogID] [int] NOT NULL,

CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED

(

[session_id] ASC,

[ProcessLogID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [Table2Scheme]([ProcessLogID])

) ON [Table2Scheme]([ProcessLogID])

GO

insert into [Table1]

select 1, 443

insert into [Table1]

select 2, 444

insert into [Table1]

select 3, 445

insert into [Table2]

select 4, 440

insert into [Table2]

select 5, 441

insert into [Table2]

select 6, 442

ALTER TABLE [Table1] SWITCH PARTITION 1 to [Table2] PARTITION 4

I'd really appreciate any advice anyone has! Thanks so much.

Jess

Some further info on this

I discovered some minor changes to the example above that would get this working. I don't understand why, on a conceptual level, these changes would make a difference. Any ideas?

The 2 changes that make my above code work:

1)Declare the partition ranges using RIGHT instead of LEFT:

(i.e.

CREATE PARTITION FUNCTION [Table1Range](int) AS RANGE RIGHT FOR VALUES (443, 444, 445)

CREATE PARTITION FUNCTION [Table2Range](int) AS RANGE RIGHT FOR VALUES (440, 441, 442, 443))

2) Run the Alter Table Switch statements on Partition #s 2 & 5 instead of #s 1 & 4

(i.e. "ALTER TABLE [Table1] SWITCH PARTITION 2 to [Table2] PARTITION 5")

It's going to be a ROYAL PITA to switch the partition functions from RIGHT to LEFT in the db I'm working with. Is there anyway I can get this working keeping the LEFT definition?

Thanks!

Jess

|||

Alright- I've answered my own question. I wanted to post in case anyone runs into the same error.

I've got it working using the LEFT boundary condition by adding a Check Constraint on Table1.

ALTER TABLE Table1

ADD CONSTRAINT [CK_ProcessLogID]

CHECK ([ProcessLogID] >= 443)

Basically, when I declared the Partition Function connected to table 1 as:

CREATE PARTITION FUNCTION [Table1Range](int) AS RANGE LEFT FOR VALUES (443, 444, 445)

I am declaring Partion Number 1 to store all data where the ProcessLogID<= 443. The key is the LESS THAN or = 443. I was getting confused because in this particular example there was no data that was less than 443 in the table- but there was nothing in the table definition that prohibited it.

Partition Number 4 of Table2 is defined to be all data where the ProcessLogID> = 443 and ProcessLogID < 444 (or ProcessLogID = 443, since it is an integer column). Since a switch statement is actually just altering metadata there can be no data validation, and the definition of the table needs to be representitive that switching one partition to another will follow with the table's partition definition. By adding the check constraint to Table1, you can be assured that all data in Partition #1 of Table1 will be consistant with Partition #4 of Table2. Yippee!