locked
partition table doesn't align with data RRS feed

  • Question

  • ( Sorry, if you've found this posted several times, there is an error with the usergroup's posting )

    I have a partition table.  Each data insert is supposed to be in the correct partition like:
    partition_number    PartNum
    1                    1
    2                    2
    3                    3
    .
    .
    .
    10                    10
    11                     11
    .
    .

    It is not working like that, the following example show the result.  Data for part_num=5 is actually in part 4.
    How to correct it?

    use master
    go

    drop database ods_pt2
    go

    create database ods_pt2
    go

    use ods_pt2
    go

    CREATE SCHEMA test AUTHORIZATION [dbo]
    go

    Create    Partition Function pf_PartNum

    (int)
        As Range left For Values
        ( 1, 2, 3 );
    Go

    Create Partition Scheme ps_PartNumAllToPrimary

        As Partition pf_PartNum
        All To ([Primary]);
    Go

    create table test.MyTbl
    (
        PartNum    int
        ,sd            date
             CONSTRAINT [PK_MyTbl_PartNum] PRIMARY KEY  CLUSTERED ( PartNum ASC )
    )
    ON ps_PartNumAllToPrimary( PartNum )
    WITH ( DATA_COMPRESSION = PAGE )


    Select $partition.pf_PartNum(PartNum)  As 'partition_number' , *
    FROM    test.MyTbl
    ;
    SELECT table_name=object_name(object_id), partition_number, [rows] -- 4 partitons created. all empty
    FROM   sys.partitions
    WHERE object_id = Object_id ( 'test.MyTbl' )

    insert into test.MyTbl
    select    1    , '2001-01-01'
    union
    select    2    , '2002-2-2'
    union
    select    3    , '2003-3-3'

    Select $partition.pf_PartNum(PartNum)  As 'partition_number' , *   -- 1-3 partitions occuppied.
    FROM    test.MyTbl
    ;
    SELECT table_name=object_name(object_id), partition_number, [rows] --
    FROM   sys.partitions
    WHERE object_id = Object_id ( 'test.MyTbl' )

        ALTER PARTITION FUNCTION pf_PartNum() SPLIT RANGE ( 5 );  -- max partition number
        ALTER PARTITION scheme ps_PartNumAllToPrimary NEXT USED [Primary];

    insert into test.MyTbl
    select    4    , '2004-4-4'        --  insert into partition 4.

    Select $partition.pf_PartNum(PartNum)  As 'partition_number' , *   -- 1-4 partitions occuppied.
    FROM    test.MyTbl
    ;
    SELECT table_name=object_name(object_id), partition_number, [rows] --
    FROM   sys.partitions
    WHERE object_id = Object_id ( 'test.MyTbl' )

        ALTER PARTITION FUNCTION pf_PartNum() SPLIT RANGE ( 6 );
        ALTER PARTITION scheme ps_PartNumAllToPrimary NEXT USED [Primary];

    insert into test.MyTbl
    select    5    , '2005-5-5' --  insert into partition 4, again. It needs to be in partition 5!!

    Select $partition.pf_PartNum(PartNum)  As 'partition_number' , *   -- 1-4 partitions occuppied. partition 5 is not occupied!!
    FROM    test.MyTbl
    ;
    SELECT table_name=object_name(object_id), partition_number, [rows] --
    FROM   sys.partitions
    WHERE object_id = Object_id ( 'test.MyTbl' )




    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Friday, November 2, 2012 8:32 PM

All replies

  • You have missed a partition in the middle, instead you need to:

     ALTER PARTITION FUNCTION pf_PartNum() SPLIT RANGE ( 4 ); 
     ALTER PARTITION scheme ps_PartNumAllToPrimary NEXT USED [Primary];

    As you use the Range Left when you split 5 it created a partition which is Greater than 3 and <= 5, as partition 4 and anything > 5 as partition 5, which is out of the alignment of your needs.


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCSE: Data Platform | MCITP: BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!


    Friday, November 2, 2012 11:49 PM