How to add more partitions to existed table ?

  • I have a table that contains records of transactions with ID column is primary key

    I use partition follow ID column, each partition have 1 million records.

    CREATE PARTITION FUNCTION [pfTBLTRANS_ID](int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000, 4000000, 5000000, 6000000, 7000000, 8000000, 9000000, 10000000)

    CREATE PARTITION SCHEME [psTBLTRANS_ID] AS PARTITION [pfTBLTRANS_ID] TO ([GROUP01], [GROUP02], [GROUP03], [GROUP04], [GROUP05], [GROUP06], [GROUP07], [GROUP08], [GROUP09], [GROUP10], [GROUP11])

    But now I have more records with IDs that are greater than 11.000.000. So how can I add more partitions to this table ?

    Thanks

  • thang_ngo_2002 (12/31/2011)


    I have a table that contains records of transactions with ID column is primary key

    I use partition follow ID column, each partition have 1 million records.

    CREATE PARTITION FUNCTION [pfTBLTRANS_ID](int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000, 4000000, 5000000, 6000000, 7000000, 8000000, 9000000, 10000000)

    CREATE PARTITION SCHEME [psTBLTRANS_ID] AS PARTITION [pfTBLTRANS_ID] TO ([GROUP01], [GROUP02], [GROUP03], [GROUP04], [GROUP05], [GROUP06], [GROUP07], [GROUP08], [GROUP09], [GROUP10], [GROUP11])

    But now I have more records with IDs that are greater than 11.000.000. So how can I add more partitions to this table ?

    Thanks

    ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, then ALTER TABLE with the same partition scheme.

  • Do you mean clause

    ALTER PARTITION FUNCTION ... SPLIT RANGE ( ....);

    But I want to add more range, not split

    Could you please give me detail SQL command of ALTER PARTITION FUNCTION and ALTER PARTITION SCHEME at that case (I want to add more 5 partitions)

    Thanks

  • Yes, I can and I will, if you post or mail me your current partion scheme.

    Happy New Year!

  • Yes Sir

    It's here

    CREATE PARTITION FUNCTION [pfTBLTRANS_ID](int) AS RANGE LEFT FOR VALUES (1000000, 2000000, 3000000, 4000000, 5000000, 6000000, 7000000, 8000000, 9000000, 10000000)

    CREATE PARTITION SCHEME [psTBLTRANS_ID] AS PARTITION [pfTBLTRANS_ID] TO ([GROUP01], [GROUP02], [GROUP03], [GROUP04], [GROUP05], [GROUP06], [GROUP07], [GROUP08], [GROUP09], [GROUP10], [GROUP11])

    Thanks

  • Try following commands on Test Server... Once satisfied move it PROD.

    ALTER PARTITION FUNCTION [pfTBLTRANS_ID]()

    SPLIT RANGE (11000000) -- New Range

    GO

    ALTER PARTITION SCHEME [psTBLTRANS_ID]

    NEXT USED [ filegroup_name ] [ ; ] -- New File Group for the Partition

  • You've beaten me to it, Dev - thanks!

  • Thanks a lot, it work well

    Just funny that when I create SQL command from partition scheme in SQL Studio Management

    The sequence of groups is not in correct order

    CREATE PARTITION SCHEME [psTBLTRANS_ID] AS PARTITION [pfTBLTRANS_ID] TO ([GROUP01], [GROUP02], [GROUP03], [GROUP04], [GROUP05], [GROUP06], [GROUP07], [GROUP08], [GROUP09], [GROUP10], [GROUP12], [GROUP11])

  • I was wondering if you got the answer to your question about adding new partitions w/o splitting the the range ?

    How do you add many partitions to an existing table?

    Thx

  • You always have to modify the partitioning scheme and ALTER the table. The ALTER statement rejiggles all partitions, whether they already exist or have been just added.

  • Dev (1/1/2012)


    Try following commands on Test Server... Once satisfied move it PROD.

    ALTER PARTITION FUNCTION [pfTBLTRANS_ID]()

    SPLIT RANGE (11000000) -- New Range

    GO

    ALTER PARTITION SCHEME [psTBLTRANS_ID]

    NEXT USED [ filegroup_name ] [ ; ] -- New File Group for the Partition

    These two commands should be issued in the reverse order: set the NEXT USED partition before performing the SPLIT.

  • dao-434438 (1/15/2012) Via Private Message


    Thanks for your reply,

    I need to add 20 additional partitions to an existing table , do I have execute those commands 20 times

    and then Alter the partitioned table to use the Altered PF and PS functions?

    thx

    ===========================================================================

    Try following commands on Test Server... Once satisfied move it PROD.

    ALTER PARTITION FUNCTION [pfTBLTRANS_ID]()

    SPLIT RANGE (11000000) -- New Range

    GO

    ALTER PARTITION SCHEME [psTBLTRANS_ID]

    NEXT USED [ filegroup_name ] [ ; ] -- New File Group for the Partition

  • You can ALTER (redefine)( the function and the scheme in one shot, and then use the updated scheme to ALTER table.

    Chances are it will be faster than modifying the function and schema in single shots. (Your individual mileage may vary.)

  • dao-434438 (1/15/2012) Via Private Message


    I need to add 20 additional partitions to an existing table , do I have execute those commands 20 times and then Alter the partitioned table to use the Altered PF and PS functions?

    The following script demonstrates how this works, read the comments and run each statement one at a time:

    USE tempdb

    GO

    -- Two boundaries = three ranges

    CREATE PARTITION FUNCTION PF(integer) AS RANGE RIGHT

    FOR VALUES (1000, 5000);

    GO

    -- All to [PRIMARY] just to make the test easier

    CREATE PARTITION SCHEME PS AS PARTITION PF ALL TO ([PRIMARY]);

    GO

    -- Test table

    CREATE TABLE dbo.Partitioned

    (

    IntValue integer NOT NULL,

    StringValue varchar(50) NOT NULL,

    DateValue date NOT NULL,

    CONSTRAINT [PK dbo.Partitioned IntValue]

    PRIMARY KEY (IntValue)

    ON PS (IntValue)

    );

    GO

    -- Test data

    INSERT dbo.Partitioned

    (IntValue, StringValue, DateValue)

    VALUES

    (999, '100', '2012-01-01'),

    (1999, '200', '2012-01-02'),

    (2999, '300', '2012-01-03'),

    (3999, '400', '2012-01-04'),

    (4999, '500', '2012-01-05'),

    (5999, '600', '2012-01-06');

    GO

    -- Show the two partiton boundaries and the range values

    SELECT

    prv.boundary_id,

    prv.value

    FROM sys.partition_functions AS pf

    JOIN sys.partition_range_values AS prv ON

    prv.function_id = pf.function_id

    WHERE

    pf.name = N'PF'

    ORDER BY

    prv.boundary_id;

    -- Show the three partitons, and number of rows in each

    SELECT

    p.partition_number,

    p.rows

    FROM sys.partitions AS p

    WHERE

    p.object_id = OBJECT_ID(N'dbo.Partitioned')

    ORDER BY

    p.partition_number;

    -- Show partitions and values another way

    SELECT

    $PARTITION.PF(p.IntValue) AS partition_id,

    p.*

    FROM dbo.Partitioned AS p

    ORDER BY

    p.IntValue;

    -- Set the next file group to use for a new partition

    ALTER PARTITION SCHEME PS

    NEXT USED [PRIMARY];

    -- Create a new partition at IntValue = 2000

    ALTER PARTITION FUNCTION PF()

    SPLIT RANGE (2000);

    -- Notice the table has automatically been updated

    -- There are now four partitions

    SELECT

    $PARTITION.PF(p.IntValue) AS partition_id,

    *

    FROM dbo.Partitioned AS p

    ORDER BY

    p.IntValue;

    -- Perform the remaining splits

    ALTER PARTITION SCHEME PS

    NEXT USED [PRIMARY];

    ALTER PARTITION FUNCTION PF()

    SPLIT RANGE (3000);

    ALTER PARTITION SCHEME PS

    NEXT USED [PRIMARY];

    ALTER PARTITION FUNCTION PF()

    SPLIT RANGE (4000);

    -- Six partitions

    SELECT

    $PARTITION.PF(p.IntValue) AS partition_id,

    *

    FROM dbo.Partitioned AS p

    ORDER BY

    p.IntValue;

    -- Five partiton boundaries and ranges

    SELECT

    prv.boundary_id,

    prv.value

    FROM sys.partition_functions AS pf

    JOIN sys.partition_range_values AS prv ON

    prv.function_id = pf.function_id

    WHERE

    pf.name = N'PF'

    ORDER BY

    prv.boundary_id;

    -- Show the six partitons, and number of rows in each

    SELECT

    p.partition_number,

    p.rows

    FROM sys.partitions AS p

    WHERE p.object_id = OBJECT_ID(N'dbo.Partitioned')

    ORDER BY

    p.partition_number;

    -- Tidy up

    DROP TABLE dbo.Partitioned;

    DROP PARTITION SCHEME PS;

    DROP PARTITION FUNCTION PF;

  • dao-434438 (1/15/2012)


    I was wondering if you got the answer to your question about adding new partitions w/o splitting the the range ?

    How do you add many partitions to an existing table?

    Thx

    There is no real "adding a partition," it is always splitting it. Remember that the last partition in the scheme contains all data >(= depending on left or right) boundary. So in this case, if 11,000,000 was the highest boundary then the last partition would hold all data >(=) 11,000,000. So, that partition has to be split because it contains all values to the limit of the data type. Make sense?

    Jared
    CE - Microsoft

Viewing 15 posts - 1 through 15 (of 24 total)

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