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.