Microsoft® SQL Server® 2012 Bible

1109

CHAPTER

49

Partitioning

IN THIS CHAPTER

Scaling Out with Multiple Tables and Multiple Servers

Using Distributed Partition Views

Exploring Table Partitioning

Creating Custom Partitioning Design

W

hen working with large tables — in terabytes — you are often posed with several chal-

lenges. How can you mitigate those problems? Division.

Dividing data brings several benefi ts:

■ It’s signifi cantly easier to maintain, back up, and defragment a divided data set.

■ (^) The divided data sets mean smaller indexes, fewer intermediate pages, and faster
performance.
■ (^) The divided data sets can reside on separate physical servers, thus scaling out and lowering
costs and improving performance.
However, dividing, or partitioning, data has its own set of problems to conquer. You can solve
this problem in several ways. SQL Server offers a couple of technologies that handle partitioning:
partitioned views and partitioned tables.

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.
c49.indd 1109c49.indd 1109 7/31/2012 10:24:20 AM7/31/2012 10:24:20 AM
http://www.it-ebooks.info