Microsoft® SQL Server® 2008 Bible

Chapter 68. Partitioning

IN THIS CHAPTER

• Scaling out with multiple tables and multiple servers.

• Distributed partition views

• Table partitioning

• Custom partitioning design

Divide and conquer.

Dividing a terabyte table can be as effective as dividing an enemy tank division or dividing the opposing political party.

Dividing data brings several benefits:

• It's significantly 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. E. F. Codd recognized the potential issues with physical partitioning of data in October 1985 in his famous "Is Your DBMS Really Relational?" article, which outlined 12 rules, or criteria, for a relational database. Rule 11 specifically deals with partitioned data:

Rule 11: Distribution independence

The distribution of portions of the database to various locations should be invisible to users of the database. Existing applications should continue to operate successfully:

1. when a distributed version of the DBMS is first introduced; and

2. when existing distributed data are redistributed around the system.

In layperson's terms, rule 11 says that if the complete set of data is spread over multiple tables or multiple servers, then the software must be able to search for any piece ...