Professional Microsoft® SQL Server® 2008 Administration

18.9. Archiving Data

Archiving a large amount of data from very large tables can be challenging. For example, selecting millions of rows from a billon-row table, copying them, and then deleting them is a long-running delete process that may escalate to a table lock and reduce concurrency, which is not acceptable, unless no one will be using the table. A commonly used procedure is to periodically delete a small number of rows to improve table concurrency, as the smaller number of rows may take page locks and use an index for faster access, completing faster.

An efficient procedure to archive large amounts of data is to use a sliding time window table partitioning scheme. There are two approaches to this solution: using SQL Server 2008 data partitioning or using a partitioned view.

18.9.1. SQL Server 2008 Table Partitioning

SQL Server 2008 supports table partitioning, whereby a table can be carved into as many as 1,000 pieces, with each residing on its own filegroup. Each filegroup can be independently backed up.

Let's look at a partitioning example in which each partition contains one month's data. With table partitioning, a new empty partition is created when the next monthly data becomes available. Then the oldest partition can be switched out into a table and moved to an archive table monthly. The basic steps to create a table partition are as follows:

1. Create a partition function that describes how the data is to be partitioned.

2. Create a partition schema that maps the pieces ...