O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

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

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

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