SQL Server 2005 Partitioning Script

SQL Server 2005 Partitioning Script

SQL Server 2005 Partitioning Script
In my SQL Server 2005 DBA Upgrade class I demonstrated the use of the new partitioning feature with a rolling range scenario. The rolling range scenario that I used keeps 6 months of orders and order detail data. The tables are partitioned based on orderdate where each partition holds one month of data. When a new month of data is added to the table, the oldest month of data is dropped out. The power of partitioning comes from the ability to manipulate large chunks of data in an even larger table with only metadata operations. These metadata operations are very fast. The old data can be switched out in a second. Then after loading the new months data into the proper filegroup, it can be switched into the larger multi-month table in a second. The script that I used to demonstrate partitioning is included HERE. This script demonstrates both Left and Right based partitioning functions.