SQL Server 2008 Table Partitioning

When a database table grows in size to the hundreds of gigabytes or more, it can become more difficult to load new data, remove old data, and maintain indexes. Just the sheer size of the table causes such operations to take much longer. Even the data that must be loaded or removed can be very sizable, making INSERT and DELETE operations on the table impractical. The Microsoft® SQL Server® 2008/2005 database software provides table partitioning to make such operations more manageable.


Partitioning a large table divides the table and its indexes into smaller partitions, so that maintenance operations can be applied on a partition-by-partition basis, rather than on the entire table. In addition, the SQL Server optimizer can direct properly filtered queries to appropriate partitions rather than the entire table.

Table Partitioning Overview

• Horizontal Partitioning
• Vertical Partitioning

SQL Server table Partitioning:
SQL Server's table partitioning differs from the above two approaches by partitioning a single table: Multiple physical tables are no longer involved. When a table is created as a partitioned table, SQL Server automatically places the table's rows in the correct partition, and SQL Server maintains the partitions behind the scenes.

 SQL Server's table partitioning is designed to make loading, aging, and other maintenance operations on large tables easier, as well as improve performance of properly filtered queries using partition elimination.

Benefits of Partitioned Tables

• SQL Server automatically manages the placement of data in the proper partitions.
• A partitioned table and its indexes appear as a normal database table with indexes, even though the table might have numerous partitions.
• The table can be managed at the partition and filegroup level for ease of maintenance.
• Partitioned tables support easier and faster data loading, aging, and archiving, as illustrated in the example on last slide.
• Application queries that are properly filtered on the partition column can perform better by making use of partition elimination and parallelism.
• In cases where partitioned data will not be modified, you can mark some or most of a partitioned table's filegroups as read-only, making management of the filegroups easier.
• In SQL Server 2008, you can compress individual partitions as well as control lock escalation at a partition level.

Challenges of table Partitioning

• There is a maximum of 1,000 partitions for a table.
• You must manage filegroups and file placement if you place partitions on individual filegroups.
• The metadata-only operations (SWITCH, MERGE, and SPLIT) can be blocked by other DML actions on the table at the time, until a schema-modification lock can be obtained.
• Managing date or time-based data can be complex.
• You cannot rebuild a partitioned index with the ONLINE option set to ON, because the entire table will be locked during the rebuild.
• Automating changes to partitioned tables, as in a sliding window scenario, can be difficult, but Microsoft provides some tools to assist in automating the process.