Professional SQL Server™ 2005 Programming

7.9. Partitioning for Scalability

Beginning with SQL Server 2000, you have picked up the marvelous ability to create one logical table from multiple physical tables — partitioned views. That is, the data from one logical table is partitioned such that is stored in a separate well-defined set of physical tables. But the notion of partitioning your data has been around a lot longer than partitioned views have been. Indeed, keeping your main accounting system on one server and your order entry and inventory systems on another is a form of partitioning — you are making sure that the load of handling the two activities is spread across multiple servers. SQL Server 2005 has taken an additional step by adding what are called partitioned tables.

Partitioned tables are a bit different from partitioned views in a way that is implied in their name — they truly remain a table throughout. Whereas a partitioned view could not support some of the functionality found in tables (constraints, defaults, identity columns, etc.), a partitioned table supports all these.

There is, of course, a catch — partitioned tables are limited to just one server (it is a means of separating a table across multiple file groups and, therefore, drive volumes). Partitioned views should still be used when the load is such that you need to span multiple servers. For purposes of this chapter, you're going to stick with the basic notions of partitioning that apply to both the view and table models.

Regardless of which ...