Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 226

operations, it sorts the data first by partition. This means that the database engine accesses one drive at
a time, which may negatively impact performance. In this case it's better to stripe the data files across a
RAID configuration. This way, when the database engine sorts the data by partition, it can access all the
drives of each partition at the same time.
UtilizingPartitions
Once you've decided that you need to create a partition for a table, there's a few things you'll have to
create. The first thing is file groups, the next item is a partition function, and finally a partition scheme
must be created.
File Groups
File groups are nothing more than named collections of the database files. We're not going to go into any
detail about defining file groups. BOL has plenty of documentation in that regard. However, know this;
when you're going to partition a table, you'll typically have one file group for each partition. Thus, if you
want to create three partitions for a table, you should create three file groups.
Partition Function
A partition function defines how the rows of a table or index are split among a set of partitions. This
splitting is based on the values of specified column. Note that you can only specify one partition column.
For example, an Order table may be split into multiple partitions based on the OrderDate column.
There are two things to consider when you plan a partition function. Choosing the appropriate partition-
ing column, and setting the range of values of the partitioning column for each partition. The range of
values determines the number of partitions that make up your table. Note, however, that there's a limit
to the number of partitions a table can have, which is 1,000 partitions.
Choosing the appropriate partitioning column and the range of values for each partition are the keys to
creating useful partitions.
Creating a partition function specifies how the table or index is partitioned. The function maps the
domain into a set of partitions. Creating a partition function involves specifying the number of parti-
tions, the column to partition on, and the range of values that the partition column will use on each
partition. If you want to use a computed column as a partitioning column it must be marked as persisted.
Furthermore, the following datatypes are not allowed for a partitioned column: timestamp, ntext, text,
image, xml, varchar(max), nvarchar(max), or varbinary(max). Also, CLR user-defined type and alias data
type columns cannot be specified.
Partition Scheme
A partition scheme simply assigns each partition defined by the partition function to a file group. The
main reason for putting partitions on separate file groups is that backing up those file groups can be done
independently of one another.
CreatingaPartitionedTable
With the previous three database objects defined you can now create a table that is partitioned. This
is done using the ON clause of the CREATE TABLE statement. The ON clause will designate which
partition scheme to use and which column acts as the partitioning column. Again, BOL has the detailed
syntax for this.