SQL Server: ALTER PARTITION FUNCTION statement

DBA/Developer can use the ALTER PARTITION FUNCTION statement to add a partition to an existing partitioned table, or to combine two partitions in a partitioned table. The complete syntax for the ALTER PARTITION FUNCTION statement is as follows:

ALTER PARTITION FUNCTION partition_function_name()

{SPLIT RANGE (boundary_value) | MERGE RANGE (boundary_value)};

The MERGE RANGE clause combines two partitions, and the SPLIT RANGE clause splits a partition into two partitions. Each of these clauses specifies the boundary value for which the split or merge should occur. In addition, before DBA/Developer split partitions, DBA/Developer must ensure that the partition scheme has enough filegroups to accommodate the new partition, or an error will be generated. DBA/Developer must also specify a value that is different from any other defined boundary value. DBA/Developer should not consolidate the partitions using the MERGE RANGE clause and re-partition the table. The MERGE RANGE clause is used when combining two partitions. DBA/Developer should not alter the partition scheme and rebuild the table partitions. The partition scheme maps partitions to file groups. Modifying the partition scheme would not affect the number of partitions. DBA/Developer should not create a temporary table to store the data, and then drop and re-create the table. There is no need to re-create the table. This would make the table unavailable to users and would not be the best choice.