Microsoft SQL Server 2012 Bible

Partitioned Tables and Indexes

Partitioned tables are similar to partitioned views — both involve segmenting the data. However, whereas partitioned views store the data in separate tables and use a view to access the tables, partitioned tables store the data in a segmented clustered index and use the table to access the data. In SQL Server 2012 the number of partitions you can create on a table has been increased to 15,000. Care should be taken when implementing a partitioning strategy that has a large number of partitions. In this case a minimum of 16G of RAM should be used. If not, certain operations can fail due to insufficient memory.

Partitioning tables reduces the sheer size of the clustered and nonclustered B-tree indexes, which provide the following manageability and performance benefits:

• You can quickly and efficiently access data because you are accessing only subsets of data.
• Backing up part of a table using Backup Filegroups eases backups.
• A partition's index is significantly smaller; therefore, maintenance operations on one or more partitions take less time. As a result, you can reduce the performance cost of rebuilding or re-indexing.
• The selectiveness of a WHERE clause is often improved because a partition table can segment the data.

Best Practice

The performance benefit of partitioned tables doesn't kick in until the table is extremely large — billion-row tables in terabyte-size databases. In some testing, partitioned tables actually hurt performance on smaller ...