Microsoft SQL Server 2012 Bible

Partitioning Strategies

Partitions are most effective when the partition key is a column often used to select a range of data so that a query has a good chance to address only one of the segments, such as the following:

• A company partitions data by Sales Territory.
• A school partitions data by School Year.
• A manufacturing company partitions data by departments.

The common factor among each strategy is that each enables the querying of smaller sets of data. Instead of the queries searching the entire data set, only the necessary data are queried.

Best Practice

Large, frequently accessed tables, with data that can logically be divided horizontally for the most common queries, are the best candidates for partitioning. If the table doesn't meet this criteria, don't partition the table.

In the access of data, the greatest bottleneck is reading the data from the drive. The primary benefit of partitioning tables is that a smaller partitioned table can have a greater percentage of the table cached in memory.

You can consider partitioning from two perspectives:

• Horizontal partitioning means splitting the table by rows. For example, if you have a large 5,000-row spreadsheet and split it so that rows 1 through 2,500 remain in the original spreadsheet and move rows 2,501 to 5,000 to a new, additional spreadsheet, that move would illustrate horizontal partitioning.
• Vertical partitioning splits the table by columns, segmenting some columns into a different table. Sometimes this makes ...