locked
Partitioning "Alter Table Switch" Statement Failing RRS feed

  • Question

  • 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

     

    Thursday, April 19, 2007 12:22 AM

Answers

  • 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!  Smile

    Thursday, April 19, 2007 10:49 PM

All replies

  • 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

    Thursday, April 19, 2007 9:54 PM
  • 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!  Smile

    Thursday, April 19, 2007 10:49 PM