Professional Microsoft® SQL Server® 2008 Administration

15.3. Partitioned Tables and Indexes

Now we'll get started digging into the details of some of these awesome features. First you'll learn why you would need to use each feature, and then how you should use it. Along the way, you'll discover more about what partitioned tables and indexes are and how you use them.

15.3.1. Why Use Partitioned Tables and Indexes?

Partitioned tables are a way to spread a single table over multiple partitions, and while doing so each partition can be on a separate filegroup. There are several reasons for doing this:

• Faster and easier data loading: If your database has a very large amount of data to load, you might want to consider using a partitioned table. By "a very large amount of data," we don't mean a specific amount of data, but any case in which the load operation takes longer than is acceptable in the production cycle. A partitioned table enables you to load the data to an empty table that's not in use by the "live" data, so it has less impact on concurrent live operations.

  Clearly, there will be an impact on the I/O subsystem, but if you also have separate filegroups on different physical disks, even this has a minimal impact on overall system performance.

  Once the data is loaded to the new table, you can perform a switch to add the new table to the live data. This switch is a simple metadata change that executes very quickly; that is why partitioned tables are a great way to load large amounts of data with limited impact to users who are touching ...