Planning Guidelines for Partitioned Tables and Indexes
You must plan to create the following database objects before partitioning a table or index:
Partition function
Partition scheme
A partition function defines how the rows of a table or index are mapped to a set of partitions based on the values of certain columns, called partitioning columns.
A partition scheme maps each partition specified by the partition function to a filegroup.
Planning the Partition Function
There are two factors to consider when planning a partition function: the column whose values determine how a table is partitioned, known as the partitioning column, and the range of values of the partitioning column for each partition. This range of values determines the number of partitions that make up your table. A table can have a maximum of 1,000 partitions.
The choices you have for the partitioning column and the values range are determined primarily by the extent to which your data can be grouped in a logical way, such as by date, and whether this logical grouping is adequate for managing subsets of data.
For example, under the partitioning scenario of the AdventureWorks sample database, the TransactionHistory and TransactionHistoryArchive tables are partitioned on the TransactionDate field. The range of values for each partition is one month. The TransactionHistory table maintains the year's most current transactions, while TransactionHistoryArchive maintains older transactions. By partitioning the tables in this way, a single month's worth of old data can be transferred quickly and efficiently from TransactionHistory to TransactionHistoryArchive on a monthly basis.
For more information about this specific scenario, see Designing Partitions to Manage Subsets of Data.
Any column whose data type can be used as an index key can also be specified as a partitioning column, except the timestamp data type, Microsoft .NET Framework common language runtime (CLR) user-defined data types, and alias data types. For more information, see Creating Partitioned Tables and Indexes.
Planning the Partition Scheme
In planning the partition scheme, you must decide what filegroup or filegroups that you want to put your partitions on. The primary reason for placing your partitions on separate filegroups is to make sure that you can independently perform backup operations on partitions. This is because you can perform backups on individual filegroups. For more information, see Backing Up and Restoring Databases in SQL Server.
Important
SQL Server does not guarantee completion of any queries that access a partitioned table if any files that belong to any filegroups of the table are not in an ONLINE state, regardless of which partitions are accessed by the query.