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

Get Microsoft SQL Server 2012 Bible now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.