locked
Trouble with partition switching RRS feed

  • Question

  • Greetings all.

    I have a massive table I am extracting into a staging database in sql server 2008 (windows 2003 both 64 bit). As a test I am doing 3 loads from the source into 3 separate tables that I use the partition switch to the final table from the 3 smaller tables. So far this has worked fine. What I am having issues with is switching the 2nd and 3rd table over. The 1st table works just fine.

    Here is the layout

    Staging.order_part (testing name) is the main table that is build on the partition function/scheme

    the 3 tables that are loaded from the source are
    zprt.order_1
    zprt.order_2
    zprt.order_3

    Here is the script that builds the partition function and scheme.

    CREATE PARTITION FUNCTION PF(INT)
    AS RANGE LEFT
    FOR VALUES (20081231,20091231,20101231 )
    GO
    CREATE PARTITION SCHEME PS
    AS PARTITION PF ALL TO ([FG2-2])

    Now it is my understanding that this would create 4 partitions based on these ranges

    PARTITION 1 <= 20081231
    PARTITION 2 > 20081231 AND <= 20091231
    PARTITION 3 > 20091231 AND <=20101231
    PARTITION 4 > 20101231

    So I built the table constraints on the 3 load tables as such

    ALTER TABLE zprt.order_1 ADD CHECK (OBLMDT <= 20081231);
    ALTER TABLE zprt.order_2 ADD CHECK (OBLMDT > 20081231 AND OBLMDT <= 20091231);
    ALTER TABLE zprt.order_3 ADD CHECK (OBLMDT > 20091231 AND OBLMDT <= 20101231 );

    And issue the following commands

    ALTER TABLE zprt.order_1 SWITCH TO staging.order_part partition 1
    ALTER TABLE zprt.order_2 SWITCH TO staging.order_part partition 2
    ALTER TABLE zprt.order_3 SWITCH TO staging.order_part partition 3

    The first one completes fine however the second and third give me the following error;

    ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'Zprt.order_2'
    allows values that are not allowed by check constraints or partition function on target table 'staging.order_part'.

    To validate I ran the following scripts and their following results are after them

    select min(oblmdt), max(oblmdt) from zprt.order_1 = 0 to 20081231 -- this table switch works
    select min(oblmdt), max(oblmdt) from zprt.order_2 = 20090102 to 20091231
    select min(oblmdt), max(oblmdt) from zprt.order_3 = 20100104 to 20100722

    As you can see, the ranges of each table fall within the partition function's ranges as well as the check constraints. The check constraints and partition function's parameters match up, so I am at a complete loss as to why I am getting this error.

    Any ideas?
    Thursday, July 22, 2010 11:11 PM

Answers

  • Is the partition column nullable?

    if so, then NULL values are not catched by the constraint you are using in second and third tables. The null values will be in the first partition.

     

    USE tempdb;
    GO
    CREATE PARTITION FUNCTION PF(INT)
    AS RANGE LEFT
    FOR VALUES (20081231,20091231,20101231 )
    GO
    CREATE PARTITION SCHEME PS
    AS PARTITION PF ALL TO ([PRIMARY])
    GO
    CREATE TABLE dbo.T (
    c1 int NOT NULL
    ) ON PS(c1);
    GO
    -- script from Dan Guzman
    -- http://weblogs.sqlteam.com/dang/archive/2008/12/11/Partition-Details-and-Row-Counts.aspx
    --paritioned table and index details
    SELECT
     OBJECT_NAME(p.object_id) AS ObjectName,
     i.name   AS IndexName,
     p.index_id  AS IndexID,
     ds.name   AS PartitionScheme, 
     p.partition_number AS PartitionNumber,
     fg.name   AS FileGroupName,
     prv_left.value  AS LowerBoundaryValue,
     prv_right.value  AS UpperBoundaryValue,
     CASE pf.boundary_value_on_right
      WHEN 1 THEN 'RIGHT'
      ELSE 'LEFT' END AS Range,
     p.rows AS Rows
    FROM sys.partitions   AS p
    JOIN sys.indexes   AS i
     ON i.object_id = p.object_id
     AND i.index_id = p.index_id
    JOIN sys.data_spaces   AS ds
     ON ds.data_space_id = i.data_space_id
    JOIN sys.partition_schemes  AS ps
     ON ps.data_space_id = ds.data_space_id
    JOIN sys.partition_functions  AS pf
     ON pf.function_id = ps.function_id
    JOIN sys.destination_data_spaces AS dds2
     ON dds2.partition_scheme_id = ps.data_space_id 
     AND dds2.destination_id = p.partition_number
    JOIN sys.filegroups   AS fg
     ON fg.data_space_id = dds2.data_space_id
    LEFT JOIN sys.partition_range_values AS prv_left
     ON ps.function_id = prv_left.function_id
     AND prv_left.boundary_id = p.partition_number - 1
    LEFT JOIN sys.partition_range_values AS prv_right
     ON ps.function_id = prv_right.function_id
     AND prv_right.boundary_id = p.partition_number 
    WHERE
     OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
    ORDER BY
     ObjectName,
     IndexID,
     PartitionNumber;
    GO
    CREATE TABLE dbo.T1 (
    c1 int NOT NULL,
    CONSTRAINT CK_T1_c1 CHECK (c1 <= 20081231)
    ) ON [PRIMARY];
    GO
    CREATE TABLE dbo.T2 (
    c1 int NOT NULL,
    CONSTRAINT CK_T2_c1 CHECK (c1 > 20081231 AND c1 <= 20091231)
    ) ON [PRIMARY];
    GO
    CREATE TABLE dbo.T3 (
    c1 int NOT NULL,
    CONSTRAINT CK_T3_c1 CHECK (c1 > 20091231 AND c1 <= 20101231)
    ) ON [PRIMARY];
    GO
    INSERT INTO dbo.T1(c1) VALUES(20081231);
    GO
    INSERT INTO dbo.T2(c1) VALUES(20091231);
    GO
    INSERT INTO dbo.T3(c1) VALUES(20101231);
    GO
    ALTER TABLE dbo.T1 SWITCH TO dbo.T PARTITION 1;
    GO
    --paritioned table and index details
    SELECT
     OBJECT_NAME(p.object_id) AS ObjectName,
     i.name   AS IndexName,
     p.index_id  AS IndexID,
     ds.name   AS PartitionScheme, 
     p.partition_number AS PartitionNumber,
     fg.name   AS FileGroupName,
     prv_left.value  AS LowerBoundaryValue,
     prv_right.value  AS UpperBoundaryValue,
     CASE pf.boundary_value_on_right
      WHEN 1 THEN 'RIGHT'
      ELSE 'LEFT' END AS Range,
     p.rows AS Rows
    FROM sys.partitions   AS p
    JOIN sys.indexes   AS i
     ON i.object_id = p.object_id
     AND i.index_id = p.index_id
    JOIN sys.data_spaces   AS ds
     ON ds.data_space_id = i.data_space_id
    JOIN sys.partition_schemes  AS ps
     ON ps.data_space_id = ds.data_space_id
    JOIN sys.partition_functions  AS pf
     ON pf.function_id = ps.function_id
    JOIN sys.destination_data_spaces AS dds2
     ON dds2.partition_scheme_id = ps.data_space_id 
     AND dds2.destination_id = p.partition_number
    JOIN sys.filegroups   AS fg
     ON fg.data_space_id = dds2.data_space_id
    LEFT JOIN sys.partition_range_values AS prv_left
     ON ps.function_id = prv_left.function_id
     AND prv_left.boundary_id = p.partition_number - 1
    LEFT JOIN sys.partition_range_values AS prv_right
     ON ps.function_id = prv_right.function_id
     AND prv_right.boundary_id = p.partition_number 
    WHERE
     OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
    ORDER BY
     ObjectName,
     IndexID,
     PartitionNumber;
    GO
    ALTER TABLE dbo.T2 SWITCH TO dbo.T PARTITION 2;
    GO
    ALTER TABLE dbo.T3 SWITCH TO dbo.T PARTITION 3;
    GO
    DROP TABLE dbo.T, dbo.T1, dbo.T2, dbo.T3;
    GO
    DROP PARTITION SCHEME PS;
    GO
    DROP PARTITION FUNCTION PF;
    GO
    

    If you change the nullability of [c1] in each of the four tables, then you get same error as yours. To catch NULL values then you will have to expand the constraint in tables dbo.T2 and dbo.T3, like for example, "CONSTRAINT CK_T2_c1 CHECK (c1 IS NOT NULL AND c1 > 20081231 AND c1 <= 20091231)".

    USE tempdb;
    GO
    CREATE PARTITION FUNCTION PF(INT)
    AS RANGE LEFT
    FOR VALUES (20081231,20091231,20101231 )
    GO
    CREATE PARTITION SCHEME PS
    AS PARTITION PF ALL TO ([PRIMARY])
    GO
    CREATE TABLE dbo.T (
    c1 int NULL
    ) ON PS(c1);
    GO
    -- script from Dan Guzman
    -- http://weblogs.sqlteam.com/dang/archive/2008/12/11/Partition-Details-and-Row-Counts.aspx
    --paritioned table and index details
    SELECT
       OBJECT_NAME(p.object_id) AS ObjectName,
       i.name          AS IndexName,
       p.index_id        AS IndexID,
       ds.name         AS PartitionScheme,  
       p.partition_number    AS PartitionNumber,
       fg.name         AS FileGroupName,
       prv_left.value      AS LowerBoundaryValue,
       prv_right.value     AS UpperBoundaryValue,
       CASE pf.boundary_value_on_right
          WHEN 1 THEN 'RIGHT'
          ELSE 'LEFT' END  AS Range,
       p.rows AS Rows
    FROM sys.partitions         AS p
    JOIN sys.indexes           AS i
       ON i.object_id = p.object_id
       AND i.index_id = p.index_id
    JOIN sys.data_spaces         AS ds
       ON ds.data_space_id = i.data_space_id
    JOIN sys.partition_schemes      AS ps
       ON ps.data_space_id = ds.data_space_id
    JOIN sys.partition_functions     AS pf
       ON pf.function_id = ps.function_id
    JOIN sys.destination_data_spaces   AS dds2
       ON dds2.partition_scheme_id = ps.data_space_id 
       AND dds2.destination_id = p.partition_number
    JOIN sys.filegroups         AS fg
       ON fg.data_space_id = dds2.data_space_id
    LEFT JOIN sys.partition_range_values AS prv_left
       ON ps.function_id = prv_left.function_id
       AND prv_left.boundary_id = p.partition_number - 1
    LEFT JOIN sys.partition_range_values AS prv_right
       ON ps.function_id = prv_right.function_id
       AND prv_right.boundary_id = p.partition_number 
    WHERE
       OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
    ORDER BY
       ObjectName,
       IndexID,
       PartitionNumber;
    GO
    CREATE TABLE dbo.T1 (
    c1 int NULL,
    CONSTRAINT CK_T1_c1 CHECK (c1 <= 20081231)
    ) ON [PRIMARY];
    GO
    CREATE TABLE dbo.T2 (
    c1 int NULL,
    CONSTRAINT CK_T2_c1 CHECK (c1 IS NOT NULL AND c1 > 20081231 AND c1 <= 20091231)
    ) ON [PRIMARY];
    GO
    CREATE TABLE dbo.T3 (
    c1 int NULL,
    CONSTRAINT CK_T3_c1 CHECK (c1 IS NOT NULL AND c1 > 20091231 AND c1 <= 20101231)
    ) ON [PRIMARY];
    GO
    INSERT INTO dbo.T1(c1) VALUES(20081231);
    GO
    INSERT INTO dbo.T2(c1) VALUES(20091231);
    GO
    INSERT INTO dbo.T3(c1) VALUES(20101231);
    GO
    ALTER TABLE dbo.T1 SWITCH TO dbo.T PARTITION 1;
    GO
    --paritioned table and index details
    SELECT
       OBJECT_NAME(p.object_id) AS ObjectName,
       i.name          AS IndexName,
       p.index_id        AS IndexID,
       ds.name         AS PartitionScheme,  
       p.partition_number    AS PartitionNumber,
       fg.name         AS FileGroupName,
       prv_left.value      AS LowerBoundaryValue,
       prv_right.value     AS UpperBoundaryValue,
       CASE pf.boundary_value_on_right
          WHEN 1 THEN 'RIGHT'
          ELSE 'LEFT' END  AS Range,
       p.rows AS Rows
    FROM sys.partitions         AS p
    JOIN sys.indexes           AS i
       ON i.object_id = p.object_id
       AND i.index_id = p.index_id
    JOIN sys.data_spaces         AS ds
       ON ds.data_space_id = i.data_space_id
    JOIN sys.partition_schemes      AS ps
       ON ps.data_space_id = ds.data_space_id
    JOIN sys.partition_functions     AS pf
       ON pf.function_id = ps.function_id
    JOIN sys.destination_data_spaces   AS dds2
       ON dds2.partition_scheme_id = ps.data_space_id 
       AND dds2.destination_id = p.partition_number
    JOIN sys.filegroups         AS fg
       ON fg.data_space_id = dds2.data_space_id
    LEFT JOIN sys.partition_range_values AS prv_left
       ON ps.function_id = prv_left.function_id
       AND prv_left.boundary_id = p.partition_number - 1
    LEFT JOIN sys.partition_range_values AS prv_right
       ON ps.function_id = prv_right.function_id
       AND prv_right.boundary_id = p.partition_number 
    WHERE
       OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
    ORDER BY
       ObjectName,
       IndexID,
       PartitionNumber;
    GO
    ALTER TABLE dbo.T2 SWITCH TO dbo.T PARTITION 2;
    GO
    ALTER TABLE dbo.T3 SWITCH TO dbo.T PARTITION 3;
    GO
    DROP TABLE dbo.T, dbo.T1, dbo.T2, dbo.T3;
    GO
    DROP PARTITION SCHEME PS;
    GO
    DROP PARTITION FUNCTION PF;
    GO
    

    I suggest that you keep first and last partitions empty, so you can merge / split partition in the future without moving data from one partition to another.

    Partitioned Table and Index Strategies Using SQL Server 2008

    AMB

    • Marked as answer by shannonjk Friday, July 23, 2010 9:20 PM
    Friday, July 23, 2010 2:12 PM

All replies

  • Is the partition column nullable?

    if so, then NULL values are not catched by the constraint you are using in second and third tables. The null values will be in the first partition.

     

    USE tempdb;
    GO
    CREATE PARTITION FUNCTION PF(INT)
    AS RANGE LEFT
    FOR VALUES (20081231,20091231,20101231 )
    GO
    CREATE PARTITION SCHEME PS
    AS PARTITION PF ALL TO ([PRIMARY])
    GO
    CREATE TABLE dbo.T (
    c1 int NOT NULL
    ) ON PS(c1);
    GO
    -- script from Dan Guzman
    -- http://weblogs.sqlteam.com/dang/archive/2008/12/11/Partition-Details-and-Row-Counts.aspx
    --paritioned table and index details
    SELECT
     OBJECT_NAME(p.object_id) AS ObjectName,
     i.name   AS IndexName,
     p.index_id  AS IndexID,
     ds.name   AS PartitionScheme, 
     p.partition_number AS PartitionNumber,
     fg.name   AS FileGroupName,
     prv_left.value  AS LowerBoundaryValue,
     prv_right.value  AS UpperBoundaryValue,
     CASE pf.boundary_value_on_right
      WHEN 1 THEN 'RIGHT'
      ELSE 'LEFT' END AS Range,
     p.rows AS Rows
    FROM sys.partitions   AS p
    JOIN sys.indexes   AS i
     ON i.object_id = p.object_id
     AND i.index_id = p.index_id
    JOIN sys.data_spaces   AS ds
     ON ds.data_space_id = i.data_space_id
    JOIN sys.partition_schemes  AS ps
     ON ps.data_space_id = ds.data_space_id
    JOIN sys.partition_functions  AS pf
     ON pf.function_id = ps.function_id
    JOIN sys.destination_data_spaces AS dds2
     ON dds2.partition_scheme_id = ps.data_space_id 
     AND dds2.destination_id = p.partition_number
    JOIN sys.filegroups   AS fg
     ON fg.data_space_id = dds2.data_space_id
    LEFT JOIN sys.partition_range_values AS prv_left
     ON ps.function_id = prv_left.function_id
     AND prv_left.boundary_id = p.partition_number - 1
    LEFT JOIN sys.partition_range_values AS prv_right
     ON ps.function_id = prv_right.function_id
     AND prv_right.boundary_id = p.partition_number 
    WHERE
     OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
    ORDER BY
     ObjectName,
     IndexID,
     PartitionNumber;
    GO
    CREATE TABLE dbo.T1 (
    c1 int NOT NULL,
    CONSTRAINT CK_T1_c1 CHECK (c1 <= 20081231)
    ) ON [PRIMARY];
    GO
    CREATE TABLE dbo.T2 (
    c1 int NOT NULL,
    CONSTRAINT CK_T2_c1 CHECK (c1 > 20081231 AND c1 <= 20091231)
    ) ON [PRIMARY];
    GO
    CREATE TABLE dbo.T3 (
    c1 int NOT NULL,
    CONSTRAINT CK_T3_c1 CHECK (c1 > 20091231 AND c1 <= 20101231)
    ) ON [PRIMARY];
    GO
    INSERT INTO dbo.T1(c1) VALUES(20081231);
    GO
    INSERT INTO dbo.T2(c1) VALUES(20091231);
    GO
    INSERT INTO dbo.T3(c1) VALUES(20101231);
    GO
    ALTER TABLE dbo.T1 SWITCH TO dbo.T PARTITION 1;
    GO
    --paritioned table and index details
    SELECT
     OBJECT_NAME(p.object_id) AS ObjectName,
     i.name   AS IndexName,
     p.index_id  AS IndexID,
     ds.name   AS PartitionScheme, 
     p.partition_number AS PartitionNumber,
     fg.name   AS FileGroupName,
     prv_left.value  AS LowerBoundaryValue,
     prv_right.value  AS UpperBoundaryValue,
     CASE pf.boundary_value_on_right
      WHEN 1 THEN 'RIGHT'
      ELSE 'LEFT' END AS Range,
     p.rows AS Rows
    FROM sys.partitions   AS p
    JOIN sys.indexes   AS i
     ON i.object_id = p.object_id
     AND i.index_id = p.index_id
    JOIN sys.data_spaces   AS ds
     ON ds.data_space_id = i.data_space_id
    JOIN sys.partition_schemes  AS ps
     ON ps.data_space_id = ds.data_space_id
    JOIN sys.partition_functions  AS pf
     ON pf.function_id = ps.function_id
    JOIN sys.destination_data_spaces AS dds2
     ON dds2.partition_scheme_id = ps.data_space_id 
     AND dds2.destination_id = p.partition_number
    JOIN sys.filegroups   AS fg
     ON fg.data_space_id = dds2.data_space_id
    LEFT JOIN sys.partition_range_values AS prv_left
     ON ps.function_id = prv_left.function_id
     AND prv_left.boundary_id = p.partition_number - 1
    LEFT JOIN sys.partition_range_values AS prv_right
     ON ps.function_id = prv_right.function_id
     AND prv_right.boundary_id = p.partition_number 
    WHERE
     OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
    ORDER BY
     ObjectName,
     IndexID,
     PartitionNumber;
    GO
    ALTER TABLE dbo.T2 SWITCH TO dbo.T PARTITION 2;
    GO
    ALTER TABLE dbo.T3 SWITCH TO dbo.T PARTITION 3;
    GO
    DROP TABLE dbo.T, dbo.T1, dbo.T2, dbo.T3;
    GO
    DROP PARTITION SCHEME PS;
    GO
    DROP PARTITION FUNCTION PF;
    GO
    

    If you change the nullability of [c1] in each of the four tables, then you get same error as yours. To catch NULL values then you will have to expand the constraint in tables dbo.T2 and dbo.T3, like for example, "CONSTRAINT CK_T2_c1 CHECK (c1 IS NOT NULL AND c1 > 20081231 AND c1 <= 20091231)".

    USE tempdb;
    GO
    CREATE PARTITION FUNCTION PF(INT)
    AS RANGE LEFT
    FOR VALUES (20081231,20091231,20101231 )
    GO
    CREATE PARTITION SCHEME PS
    AS PARTITION PF ALL TO ([PRIMARY])
    GO
    CREATE TABLE dbo.T (
    c1 int NULL
    ) ON PS(c1);
    GO
    -- script from Dan Guzman
    -- http://weblogs.sqlteam.com/dang/archive/2008/12/11/Partition-Details-and-Row-Counts.aspx
    --paritioned table and index details
    SELECT
       OBJECT_NAME(p.object_id) AS ObjectName,
       i.name          AS IndexName,
       p.index_id        AS IndexID,
       ds.name         AS PartitionScheme,  
       p.partition_number    AS PartitionNumber,
       fg.name         AS FileGroupName,
       prv_left.value      AS LowerBoundaryValue,
       prv_right.value     AS UpperBoundaryValue,
       CASE pf.boundary_value_on_right
          WHEN 1 THEN 'RIGHT'
          ELSE 'LEFT' END  AS Range,
       p.rows AS Rows
    FROM sys.partitions         AS p
    JOIN sys.indexes           AS i
       ON i.object_id = p.object_id
       AND i.index_id = p.index_id
    JOIN sys.data_spaces         AS ds
       ON ds.data_space_id = i.data_space_id
    JOIN sys.partition_schemes      AS ps
       ON ps.data_space_id = ds.data_space_id
    JOIN sys.partition_functions     AS pf
       ON pf.function_id = ps.function_id
    JOIN sys.destination_data_spaces   AS dds2
       ON dds2.partition_scheme_id = ps.data_space_id 
       AND dds2.destination_id = p.partition_number
    JOIN sys.filegroups         AS fg
       ON fg.data_space_id = dds2.data_space_id
    LEFT JOIN sys.partition_range_values AS prv_left
       ON ps.function_id = prv_left.function_id
       AND prv_left.boundary_id = p.partition_number - 1
    LEFT JOIN sys.partition_range_values AS prv_right
       ON ps.function_id = prv_right.function_id
       AND prv_right.boundary_id = p.partition_number 
    WHERE
       OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
    ORDER BY
       ObjectName,
       IndexID,
       PartitionNumber;
    GO
    CREATE TABLE dbo.T1 (
    c1 int NULL,
    CONSTRAINT CK_T1_c1 CHECK (c1 <= 20081231)
    ) ON [PRIMARY];
    GO
    CREATE TABLE dbo.T2 (
    c1 int NULL,
    CONSTRAINT CK_T2_c1 CHECK (c1 IS NOT NULL AND c1 > 20081231 AND c1 <= 20091231)
    ) ON [PRIMARY];
    GO
    CREATE TABLE dbo.T3 (
    c1 int NULL,
    CONSTRAINT CK_T3_c1 CHECK (c1 IS NOT NULL AND c1 > 20091231 AND c1 <= 20101231)
    ) ON [PRIMARY];
    GO
    INSERT INTO dbo.T1(c1) VALUES(20081231);
    GO
    INSERT INTO dbo.T2(c1) VALUES(20091231);
    GO
    INSERT INTO dbo.T3(c1) VALUES(20101231);
    GO
    ALTER TABLE dbo.T1 SWITCH TO dbo.T PARTITION 1;
    GO
    --paritioned table and index details
    SELECT
       OBJECT_NAME(p.object_id) AS ObjectName,
       i.name          AS IndexName,
       p.index_id        AS IndexID,
       ds.name         AS PartitionScheme,  
       p.partition_number    AS PartitionNumber,
       fg.name         AS FileGroupName,
       prv_left.value      AS LowerBoundaryValue,
       prv_right.value     AS UpperBoundaryValue,
       CASE pf.boundary_value_on_right
          WHEN 1 THEN 'RIGHT'
          ELSE 'LEFT' END  AS Range,
       p.rows AS Rows
    FROM sys.partitions         AS p
    JOIN sys.indexes           AS i
       ON i.object_id = p.object_id
       AND i.index_id = p.index_id
    JOIN sys.data_spaces         AS ds
       ON ds.data_space_id = i.data_space_id
    JOIN sys.partition_schemes      AS ps
       ON ps.data_space_id = ds.data_space_id
    JOIN sys.partition_functions     AS pf
       ON pf.function_id = ps.function_id
    JOIN sys.destination_data_spaces   AS dds2
       ON dds2.partition_scheme_id = ps.data_space_id 
       AND dds2.destination_id = p.partition_number
    JOIN sys.filegroups         AS fg
       ON fg.data_space_id = dds2.data_space_id
    LEFT JOIN sys.partition_range_values AS prv_left
       ON ps.function_id = prv_left.function_id
       AND prv_left.boundary_id = p.partition_number - 1
    LEFT JOIN sys.partition_range_values AS prv_right
       ON ps.function_id = prv_right.function_id
       AND prv_right.boundary_id = p.partition_number 
    WHERE
       OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
    ORDER BY
       ObjectName,
       IndexID,
       PartitionNumber;
    GO
    ALTER TABLE dbo.T2 SWITCH TO dbo.T PARTITION 2;
    GO
    ALTER TABLE dbo.T3 SWITCH TO dbo.T PARTITION 3;
    GO
    DROP TABLE dbo.T, dbo.T1, dbo.T2, dbo.T3;
    GO
    DROP PARTITION SCHEME PS;
    GO
    DROP PARTITION FUNCTION PF;
    GO
    

    I suggest that you keep first and last partitions empty, so you can merge / split partition in the future without moving data from one partition to another.

    Partitioned Table and Index Strategies Using SQL Server 2008

    AMB

    • Marked as answer by shannonjk Friday, July 23, 2010 9:20 PM
    Friday, July 23, 2010 2:12 PM
  • Thank you Hunchback!

    Simply switching my columns to not null within the create table clause resolved the problem. Thank you as well for the code, this helps greatly!

     

    This was simply a test to see if the loads would be quicker...this is a table that is truncated and reloaded each night so the partition management is fairly easy. It normally takes 10 minutes to load, when I run 3 separate data loads concurrently and use partition switching it takes 5 minutes instead...a good increase I would say!

    Friday, July 23, 2010 7:10 PM