ALTER PARTITION FUNCTION

Alter the boundary values for a partition function by splitting or merging the ranges.

Syntax
      ALTER PARTITION FUNCTION partition_function()
        SPLIT RANGE (boundary_value) [ ; ]

      ALTER PARTITION FUNCTION partition_function()
        MERGE RANGE (boundary_value) [ ; ]

Key:
   SPLIT RANGE     Add a partition to the partition function.
   MERGE RANGE     Drop a partition and merge values to one of the remaining partitions.
   boundary_value  The range of the new/old partition

The partition scheme(s) that use the partition function must have a filegroup marked as NEXT USED to hold the new partition.

If the scheme lacks a filegroup marked NEXT USED, use ALTER PARTITION SCHEME to either add a filegroup, or designate an existing one, to hold the new partition. A filegroup that already holds partitions can be designated to hold additional partitions.

Example

CREATE PARTITION FUNCTION myPtnFunct (int)
AS RANGE LEFT FOR VALUES ( 1, 50, 500 );
GO

-- Split the partition between 50 and 500
-- create 2 partitions:
--   50-250 and  250-500
ALTER PARTITION FUNCTION myPtnFunct ()
SPLIT RANGE (250);

-- Partitions at this point:
--  <=1
--  >1 and <= 50
--  >50 and <=250
--  >250 and <=500

-- Merge the partitions 1-50 and 50-250
-- to create one partition from 1-250:

ALTER PARTITION FUNCTION myPtnFunct ()
MERGE RANGE (50);

"The best way to get a bad law repealed is to enforce it strictly" ~ Abraham Lincoln

Related commands:

CREATE PARTITION FUNCTION
DROP PARTITION FUNCTION
Equivalent Oracle command: ALTER TABLE Partitioning clause