Professional Microsoft® SQL Server® 2008 Administration

12.3. Table and Index Partitioning

Simply stated, partitioning is the breaking up of a large object, such as a table, into smaller, manageable pieces. Partitioning has been around for a while. This feature received a lot of attention once it supported the ability to use constraints with views. This provided the capability for the optimizer to eliminate partitions (or tables) joined by a union of all statements on a view. These partitions could also be distributed across servers. This technology was introduced as a distributed partitioned view (DPV) during the SQL 7.0 launch.

SQL Server 2008 has the capability to partition database tables and their indexes over filegroups within a single database. This type of partitioning has many benefits over DPV, such as being transparent to the application (meaning no application code changes are necessary). Other benefits include database recoverability, simplified maintenance, and manageability.

Although we're discussing partitioning as part of this performance-tuning chapter, partitioning is first and foremost a manageability and scalability tool. In most situations, implementing partitioning also offers performance improvements as a byproduct of scalability. Several operations can be performed only on a partitioned table:

• Switch data into a partition

• Switch data out of a partition

• Merge partition range

• Split partition range

Partitioning is supported only in SQL Server 2008 Enterprise and Developer Editions.

These benefits are highlighted ...