ALTER PARTITION FUNCTION (Transact-SQL)
Alters a partition function by splitting or merging its boundary values. By executing ALTER PARTITION FUNCTION, one partition of any table or index that uses the partition function can be split into two partitions, or two partitions can be merged into one less partition.
More than one table or index can use the same partition function. ALTER PARTITION FUNCTION affects all of them in a single transaction.
Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.
ALTER PARTITION FUNCTION repartitions any tables and indexes that use the function in a single atomic operation. However, this operation occurs offline, and depending on the extent of repartitioning, may be resource-intensive.
ALTER PARTITION FUNCTION can only be used for splitting one partition into two, or merging two partitions into one. To change the way a table is otherwise partitioned (for example, from 10 partitions to 5 partitions), you can exercise any of the following options. Depending on the configuration of your system, these options can vary in resource consumption:
Create a new partitioned table with the desired partition function, and then insert the data from the old table into the new table by using an INSERT INTO...SELECT FROM statement.
Create a partitioned clustered index on a heap.
Dropping a partitioned clustered index results in a partitioned heap.
Drop and rebuild an existing partitioned index by using the Transact-SQL CREATE INDEX statement with the DROP EXISTING = ON clause.
Perform a sequence of ALTER PARTITION FUNCTION statements.
All filegroups that are affected by ALTER PARITITION FUNCTION must be online.
ALTER PARTITION FUNCTION fails when there is a disabled clustered index on any tables that use the partition function.
SQL Server does not provide replication support for modifying a partition function. Changes to a partition function in the publication database must be manually applied in the subscription database.
Any one of the following permissions can be used to execute ALTER PARTITION FUNCTION:
ALTER ANY DATASPACE permission. This permission defaults to members of the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles.
CONTROL or ALTER permission on the database in which the partition function was created.
CONTROL SERVER or ALTER ANY DATABASE permission on the server of the database in which the partition function was created.
A. Splitting a partition of a partitioned table or index into two partitions
The following example creates a partition function to partition a table or index into four partitions. ALTER PARTITION FUNCTION splits one of the partitions into two to create a total of five partitions.
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'myRangePF1') DROP PARTITION FUNCTION myRangePF1; GO CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES ( 1, 100, 1000 ); GO --Split the partition between boundary_values 100 and 1000 --to create two partitions between boundary_values 100 and 500 --and between boundary_values 500 and 1000. ALTER PARTITION FUNCTION myRangePF1 () SPLIT RANGE (500);
B. Merging two partitions of a partitioned table into one partition
The following example creates the same partition function as above, and then merges two of the partitions into one partition, for a total of three partitions.
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'myRangePF1') DROP PARTITION FUNCTION myRangePF1; GO CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES ( 1, 100, 1000 ); GO --Merge the partitions between boundary_values 1 and 100 --and between boundary_values 100 and 1000 to create one partition --between boundary_values 1 and 1000. ALTER PARTITION FUNCTION myRangePF1 () MERGE RANGE (100);