Modifying Partitioned Tables and Indexes
You can modify partitioned tables and indexes in the following ways:
- Modify a partition function to re-partition any tables or indexes that participate in it.
- Modify a partition scheme to designate a filegroup to hold a newly-added partition.
- Convert a nonpartitioned table to a partitioned table.
- Convert a partitioned table to a nonpartitioned table.
- Transfer data by adding, moving, or removing partitions
Modifying a Partition Function
You can change the way a table or index is partitioned by adding or subtracting the number of partitions specified, in increments of 1, in the partition function of the partitioned table or index.
When you add a partition, you do so by "splitting" an existing partition into two partitions and redefining the boundaries of the new partitions. When you drop a partition, you do so by "merging" the boundaries of two partitions into one. This last action repopulates one partition and leaves the other partition unassigned.
Important
When adding a partition, a filegroup must exist and be marked with the NEXT USED property to hold the new partition. For information about how to specify a NEXT USED filegroup in a partition scheme, see Modifying a Partition Scheme later in this topic.
ALTER PARTITION FUNCTION can only be used for splitting one partition into two, or for merging two partitions into one. To change the way a table or index is partitioned (from 10 partitions to 5, for example), you can use any one of the following options. Depending on the configuration of your system, these options can vary in the resource consumption they use.
Create a new partitioned table with the partition function you want, 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.
Note
Partitioned indexes that are dropped will cause partitioned heaps.
Drop and rebuild an existing partitioned index using the Transact-SQL CREATE INDEX command with the DROP EXISTING = ON clause.
Perform a sequence of ALTER PARTITION FUNCTION statements.
SQL Server 2005 does not provide replication support for modifying a partition function. If you want to make changes to a partition function in the publication database, you must do this manually in the subscription database.
To modify a partition function
Modifying a Partition Scheme
You can modify a partition scheme by designating a filegroup to hold the next partition that is added to a partitioned table. You do this by assigning the NEXT USED property to a filegroup. You can assign the NEXT USED property to an empty filegroup or to one that already holds a partition. In other words, a filegroup can hold more than one partition.
To modify a partition scheme
Converting a Nonpartitioned Table to a Partitioned Table
You can turn an existing nonpartitioned table into a partitioned table in one of two ways.
One way is to create a partitioned clustered index on the table by using the CREATE INDEX statement. This action is similar to creating a clustered index on any table, because SQL Server essentially drops the table and re-creates it in a clustered index format. If the table already has a partitioned clustered index applied to it, you can drop the index and rebuilding it on a partition scheme by using CREATE INDEX with the DROP EXISTING = ON clause.
For information about clustered indexes, see Clustered Index Design Guidelines.
Another way is to use the Transact-SQL ALTER TABLE SWITCH statement to switch the data of the table to a range-partitioned table that has only one partition. This partitioned table must already exist before the conversion occurs, and its single partition must be empty. For more information about switching partitions, see Transferring Data Efficiently by Using Partition Switching. After the table is modified as a partitioned table, you can modify its partition function to add partitions, as described previously in Modifying a Partition Function.
To convert an existing table to a partitioned table
Converting a Partitioned Table to a Nonpartitioned Table
Changing a partitioned table to a nonpartitioned table simply involves modifying the partition function of a partitioned table so that the table is made up of only one partition. Although this technically is still a partitioned table, this status is irrelevant to any subsequent operations you want to perform on the table.
If the table has a partitioned clustered index applied to it, you can obtain the same result by dropping the index and rebuilding it as a nonpartitioned index. You can do this by using the Transact-SQL CREATE INDEX command with the DROP EXISTING = ON clause.
To convert a partitioned table to a standard table with no partitions
Transferring Data with Partition Switching
The Transact-SQL ALTER TABLE...SWITCH statement lets you transfer blocks of data quickly and efficiently between partitioned tables. For more information, see Transferring Data Efficiently by Using Partition Switching.
See Also
Concepts
Implementing Partitioned Tables and Indexes