Microsoft® SQL Server® 2012 Bible

1117

Chapter 49: Partitioning

49

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

ing 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 insuffi cient memory.

Partitioning tables reduces the sheer size of the clustered and nonclustered B-tree indexes,

which provide the following manageability and performance benefi ts:

■ (^) You can quickly and effi ciently 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 signifi cantly 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 benefi t 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 tables with less than a million rows, so reserve this technology for the big problems. Maybe
that’s why table partitioning isn’t included in the Standard Edition.
On the other hand, even for tables with fewer than one million rows, partitioning can be an effective
part of archiving old data into one partition while keeping current data in another partition.
Creating SQL Server 2012 table partitions is a straightforward four-step process:

1. Create a fi legroup or fi legroups to store the partitions specifi ed by the partition
   function.


2. Create the partition function that determines how the data is partitioned.

c49.indd 1117c49.indd 1117 7/31/2012 10:24:23 AM7/31/2012 10:24:23 AM

http://www.it-ebooks.info