Post reply

Adding Partition function

  • January 2, 2009 at 12:18 pm

    #125399

    Happy new year .

    I would like to add new partition function to my existing partitioned table to store the data. how can I add function to store data from

    > 10812 < = 10903

    > 10903 < = 10906

    and so on without deletiing the whole partitioned table.

    here is the existing partition function.

    CREATE PARTITION FUNCTION [PFBTCFunction](INT)

    AS

    RANGE LEFT FOR VALUES

    ( 10512,10603,10606,10609,10612,

    10703,10706,10709,10712,10803,

    10806,10809,10812

    )

    -- Schema

    CREATE PARTITION SCHEME [PFBTCScheme]

    AS PARTITION [PFBTCFunction] TO

    (

    [FILEGROUP_BTC1], -- Filegroup for < = 10512

    [FILEGROUP_BTC2], -- Filegroup for > 10512 and < = 10603

    [FILEGROUP_BTC3], -- Filegroup for > 10603 and < = 10606

    [FILEGROUP_BTC4], -- Filegroup for > 10606 and < = 10609

    [FILEGROUP_BTC5], -- Filegroup for > 10609 and < = 10612

    [FILEGROUP_BTC6], -- Filegroup for > 10612 and < = 10703

    [FILEGROUP_BTC7], -- Filegroup for > 10703 and < = 10706

    [FILEGROUP_BTC8], -- Filegroup for > 10706 and < = 10709

    [FILEGROUP_BTC9], -- Filegroup for > 10709 and < = 10712

    [FILEGROUP_BTC10], -- Filegroup for > 10712 and < = 10803

    [FILEGROUP_BTC11], -- Filegroup for > 10803 and < = 10806

    [FILEGROUP_BTC12], -- Filegroup for > 10806 and < = 10809

    [FILEGROUP_BTC13], -- Filegroup for > 10809 and < = 10812

    [FILEGROUP_BTC14] -- Filegroup for > 10812

    )

  • January 2, 2009 at 12:47 pm

    #920495

    You need to Split the range...twice.

    1. add 1 new filegroups

    2. add a file to the filegroup

    3. Alter your partition scheme to include the added new filegroup with NEXT USED

    4. alter your partitioning function with SPLIT RANGE (10903)

    5. Repeat 1-4 for the bigger boundary 10906

    Check BOL for the exact syntax for ALTER Partition Function and Scheme.

  • January 2, 2009 at 1:52 pm

    #920536

    sunny Brook (1/2/2009)

    You need to Split the range...twice.

    1. add 1 new filegroups

    2. add a file to the filegroup

    3. Alter your partition scheme to include the added new filegroup with NEXT USED

    Alter Alter Partition Scheme PartitionSchemeName

    Next Used NewFileGroupName;

    4. alter your partitioning function with SPLIT RANGE (10903)

    Alter Partition Function Partitionfunctionname()

    Split Range ('10903')

    5. Repeat 1-4 for the bigger boundary 10906

    Check BOL for the exact syntax for ALTER Partition Function and Scheme.

    Added to what above poster said..

    MJ

  • January 2, 2009 at 1:56 pm

    #920540

    Thanks

    Yes, I am going through testing phase. I am doing the same thing what you have mentioned. One more question.

    If you noticed that in my last filegroup [FILEGROUP_BTC14] stores the data greater than 10812 ( year 2008 month 12). what happend if the data got stored 100901 (year 2009 month 01) in that file group.

    Will the split function automatically move January 2009 data to new filegroup ?

  • January 2, 2009 at 2:33 pm

    #920562

    I assume that it will automatically move the data to new partition.

    To make sure it is really the case, you can run the following before and after your splitting operation to Verify whether the rows number of the OLD last partition is changed (assuming there are data rows in there for the new partition)

    SELECT partition_number,rows,filestream_filegroup_id

    FROM sys.partitions

    WHERE OBJECT_NAME(OBJECT_ID)='YOUR Partitioned table'

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply