O'Reilly logo

Professional Microsoft® SQL Server® 2008 Administration by Steven Wort, Ross LoForte, Wayne Snyder, Ketan Patel, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required