Creating Partitioned Tables and Indexes

Creating Partitioned Tables and Ind...

Community Content

In this section

Statistics Annotations (1)

Click here to expand

Collapse All

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (November 2009)

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

CREATE PARTITION FUNCTION (Transact-SQL)

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

CREATE PARTITION SCHEME (Transact-SQL)

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

CREATE TABLE (Transact-SQL)

To create an index that uses a partition scheme

CREATE INDEX (Transact-SQL)

Concepts

Implementing Partitioned Tables and Indexes

Help and Information

Getting SQL Server 2008 Assistance

Tags

: Add a tag

Community Content

Add new content

Annotations

Example of Creating a partitioned table

Michael Freidgeim | Edit | Show History

CREATE TABLE (Transact-SQL)http://msdn.microsoft.com/en-us/library/ms174979.aspx article is quite big, I feel it will be appropriate to add an example to this article:
H: Creating a partitioned table

The following example creates a partition function to partition a table or index into four partitions. Then, the example creates a partition scheme that specifies the filegroups in which to hold each of the four partitions. Finally, the example creates a table that uses the partition scheme. This example assumes the filegroups already exist in the database.

Copy Code

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg) ;
GO
CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO