Chapter 31. Some practical issues in table partitioning

 

Ron Talmage

This chapter covers two practical topics in SQL Server table partitioning:

  • Strategies for creating partition functions
  • Minimizing data movement

Large SQL Server databases can become difficult to manage due to their size. Usually such large databases have only a couple of large tables that account for most of the databases’ size. Table partitioning is a method for making those large tables easier to manage, and in some cases improving query performance against those large tables.

Table partitioning is an involved topic and can quickly become complex; therefore, this chapter is going to have a limited scope. Let’s look at table partitioning from a general point of view, and then zero in on the two topics mentioned previously.

Table partitioning dependencies

Let’s begin with a basic overview that’ll introduce the terminology surrounding table partitioning. Our focus will be on SQL Server 2008 and SQL Server 2005. Table partitioning was introduced with SQL Server 2005 and enhanced a bit in SQL Server 2008, but the overall architecture of table partitioning remains the same between both versions.


Note

Table partitioning is an Enterprise Edition–only feature in SQL Server 2008 and 2005. That means you can use the Developer and Evaluation Editions to learn about table partitioning, but you can put it into production only with the Enterprise Edition.


Manipulating partitioned data

How the partition function works

The key: avoiding data movement

Sources for more information

Summary

About the author