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