Readme_PartitioningScript

  • Article

The purpose of the PartitioningScript sample is to demonstrate the table and index partitioning capabilities of SQL Server 2005. For information about this new feature, see "Partitioned Tables and Indexes" in SQL Server Books Online.

Important

Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples. Sample applications should not be connected to or used with your production SQL Server database without the permission of the system administrator.

Scenario

In the AdventureWorks sample database, the TransactionHistory table is used to record each sales order, purchase order, and work order transaction. Therefore, this table is one of the largest and most heavily accessed tables in the database. The TransactionHistoryArchive table was created to store older transactions from the TransactionHistory table. Data is transferred to TransactionHistoryArchive on a monthly basis for additional analysis enabling TransactionHistory to stay reasonably small and maintain current data only.

To create the partitions for these tables, the sample performs these operations:

  • Creates partition function TransactionRangePF1 on the TransactionDate column of the TransactionHistory table, so that each partition contains one month of data.
  • Creates partition scheme TransactionsPS1 to map the partitions to filegroups. In this sample, all partitions reside on the same filegroup.
  • Drops and re-creates the TransactionHistory table specifying the partition scheme TransactionsPS1 as the location for the table.
  • Creates partition function TransactionRangePF2 on the TransactionDate column of the TransactionHistoryArchive table.
  • Creates partition scheme TransactionsPS2 to map the partitions to filegroups. In this sample, all partitions reside on the same filegroup.
  • Drops and re-creates the TransactionHistoryArchive table specifying the partition scheme TransactionsPS2 as the location for the table.
  • Bulk inserts data into the two tables.

The transfer of monthly "chunks" of data between the tables is performed by switching partitions between the two tables. This typically takes just seconds, instead of minutes or hours in earlier releases, because it is a metadata operation only instead of a physical relocation of the data. Partition switching is demonstrated in the SlidingWindow sample.

Languages

Transact-SQL

Prerequisites

Before you run this sample, install SQL Server 2005 and make sure you include the following components:

  • Database Engine
  • SQL Server Management Studio
  • AdventureWorks (OLTP) sample database which is included with SQL Server 2005, and is also available at the Microsoft SQL Server Developer Center.
  • SQL Server Database Engine samples. These samples are included with SQL Server 2005. You can download the latest version of the samples at the Microsoft SQL Server Developer Center.

Running the Sample

The following procedure shows how to run the PartitioningScript sample.

To run the PartitioningScript sample

  1. In SQL Server Management Studio, open the file PartitionAW.sql. The default installation directory is C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Administration\Partitioning\Scripts, and then click Execute.

See Also

Tasks

Installing Samples

Other Resources

Partitioned Tables and Indexes
Partitioning in the AdventureWorks Sample Database

Help and Information

Getting SQL Server 2005 Assistance