Partitioning in the AdventureWorks2008R2 Sample Database
SQL Server includes two sample Transact-SQL scripts that can be run against the AdventureWorks2008R2 sample database to implement a partitioning scenario. For information about how to install and run the Readme_PartitioningScript and ReadMe_SlidingWindow scripts, see Considerations for Installing SQL Server Samples and Sample Databases.
The first script, PartitionAW.sql, partitions the AdventureWorks2008R2 tables, TransactionHistory and TransactionHistoryArchive. The TransactionHistory table contains sales records for the current year. This table is used primarily for inserting new records and updating them as required. The TransactionHistoryArchive table contains sales records older than the current year. This table is used primarily for SELECT queries and as a staging table for moving data into a data warehouse. For more information about the partitioning design of these tables, see Planning Guidelines for Partitioned Tables and Indexes.
In a real-world scenario, the TransactionHistory and TransactionHistoryArchive tables would likely become two of the largest tables of the database. By partitioning these two tables, subsets of monthly data can be managed between them. Every month, the oldest month of data is moved from TransactionHistory to TransactionHistoryArchive. In this way, data in TransactionHistory stays current for INSERT and UPDATE operations, while older data goes to TransactionHistoryArchive for scrubbing and analysis. Because the tables are partitioned, the transfer of monthly "chunks" of data between the tables typically takes just seconds, instead of the minutes or hours it took in previous releases. This is because it is a metadata operation only, instead of a physical relocation of the data.
The second script, Sliding.sql, implements this "sliding window" scenario for one month of data. For more information about how this script works, see Designing Partitions to Manage Subsets of Data.