Creating Partitioned Tables and Indexes
The steps for creating a partitioned table or index include the following:
Create a partition function to specify how a table or index that uses the function can be partitioned.
Create a partition scheme to specify the placement of the partitions of a partition function on filegroups.
Create a table or index using the partition scheme.
Creating a Partition Function
A partition function specifies how the table or index is partitioned. The function maps the domain into a set of partitions. To create a partition function, you specify the number of partitions, the partitioning column, and the range of partition column values for each partition. Note that when you specify the partitioning column, you can only specify one.
For more information about how to plan a partition function, see Planning Guidelines for Partitioned Tables and Indexes.
Partitioning Column Rules
Computed columns that participate in a partition function must be explicitly marked PERSISTED.
All data types that are valid for use as index columns can be used as a partitioning column, except timestamp. The ntext, text, image, xml, varchar(max), nvarchar(max), or varbinary(max) data types cannot be specified. Also, Microsoft .NET Framework common language runtime (CLR) user-defined type and alias data type columns cannot be specified.
To create a partition function
Creating a Partition Scheme
A partition scheme maps the partitions produced by a partition function to a set of filegroups that you define.
When you create a partition scheme, you define the filegroups where the table partitions are mapped, based on the parameters of the partition function. You must specify enough filegroups to hold the number of partitions. You can specify that all partitions map to a different filegroup, that some partitions map to a single filegroup, or that all partitions map to a single filegroup. You can also specify additional, "unassigned" filegroups in the event you want to add more partitions later. In this case, SQL Server marks one of the filegroups with the NEXT USED property. This means that the filegroup will hold the next partition that is added.
A partition scheme can use only one partition function. However, a partition function can participate in more than one partition scheme.
To create a partition scheme
Creating a Partitioned Table or Index
To partition a table or index at the time you create it, you specify the following in the CREATE TABLE or CREATE INDEX statement:
The partition scheme that the table will use to map the partitions to filegroups.
The column on which to partition the table (the partitioning column). The partitioning column must match that specified in the partition function that the partition scheme is using in terms of data type, length, and precision. If the column is computed, it must be specified as PERSISTED.
To create a table that uses a partition scheme
To create an index that uses a partition scheme