• Article

Designing Partitions to Manage Subsets of Data

By partitioning a table or index, you can move subsets of data quickly and efficiently using the Transact-SQL ALTER TABLE...SWITCH statement in the following ways:

  • Adding a table as a partition to an already-existing partitioned table.

  • Switching a partition from one partitioned table to another.

  • Removing a partition to form a single table.

These scenarios can be useful when you want to add new data to a partitioned table and remove old data from the same partitioned table on a regular basis. This operation can involve large or small amounts of data in a variety of scenarios. If new data you are adding has to be loaded, scrubbed, or transformed, it can be treated as a separate entity before adding it as a partition. Old data can be archived or warehoused. Regardless of how large or small the collection, the transfer is quick and efficient because, unlike an INSERT INTO SELECT FROM statement, the data is not physically moved. Only the metadata about where it is stored changes from one partition to another.

Example Scenario: AdventureWorks

Under the partitioning scenario of the AdventureWorks sample database, Adventure Works Cycles archives its old data from the TransactionHistory table to a TransactionHistoryArchive table by switching partitions between the two tables. They do this by partitioning TransactionHistory on the TransactionDate field. The range of values for each partition is one month. The TransactionHistory table maintains the year's most current transactions, while TransactionHistoryArchive maintains older transactions. By partitioning the tables in this way, a single month's worth of year-old data can be transferred from TransactionHistory to TransactionHistoryArchive on a monthly basis.

At the start of each month, the earliest month of data that is currently in the TransactionHistory table is switched to the TransactionHistoryArchive table. To accomplish this task, the following occurs:

  1. The TransactionHistoryArchive table must have the same design schema as the TransactionHistory table. There must also be an empty partition to receive the new data. In this case, TransactionHistoryArchive is a partitioned table that consists of just two partitions. One partition holds all data before September 2003, and the other partition holds all data from September 2003 and onward. This last partition is empty.

    Structure of tables before partitioning switching

  2. The partition function of the TransactionHistoryArchive table is modified to split its empty partition into two, with one of the partitions defined to receive the new partition for the September 2003 data.

    First step of partitioning switching

  3. The first partition of the TransactionHistory table, which contains all the data created during September 2003, is switched into the second partition of the TransactionHistoryArchive table . Note that a check constraint must be defined on the TransactionHistory table to specify no data earlier than September 1 (TransactionDate >= '9/01/2003'). This constraint makes sure that partition 1 contains only September 2003 data and that it is ready to be switched into the partition holding only the September 2003 data of the TransactionHistoryArchive table. Note also that any indexes that are not aligned with their respective tables must be dropped or disabled prior to the switch. However, they can be re-created after the switch. For more information about alignment of partitioned indexes, see Special Guidelines for Partitioned Indexes.

    Second step of partitioning switching

  4. The partition function of the TransactionHistory table is modified to merge its first two partitions into a single partition. This partition, now partition 1, contains all the data created in October 2003 and will be ready to switch into TransactionHistoryArchive the next month, provided the existing check constraint is altered to specify no data earlier than October 1 (TransactionDate >= '10/01/2003').

    Third step of partitioning switching

  5. The partition function of the TransactionHistoryArchive table is modified again to merge its second partition, which contains the September data that was just added, with its first partition. This action brings the TransactionHistoryArchive table back to its original condition in which its first partition holds all the data and its second partition is empty.

    Fourth step of partitioning switching

  6. The partition function of the TransactionHistory table is modified again to split its last partition into two partitions, so that the most current month is separated from the previous month and the partition is ready to receive new data.

    Fifth step of partitioning switching

The complete Transact-SQL script for implementing this scenario can be found at ReadMe_SlidingWindow.