Prepare the sample database for testing.
In real world, you would like to put the partition in the different file group and drive to get more performance and flexibility.
In the example, I am going to create 2 tables. TransactionHistory and TransactionHistoryArchive. TransactionHistory will have 13 partitions and TransactionHistoryArchive will have 2 partitions. After the data load to the tables. I am going to move one of the TransactionHistory partition to TransactionHistoryArchive.
Create the partition function,TransactionsPS1 and TransactionArchivePS2
Create the partition schema
Create tables and bulk insert the data
Exam partition information
Range and its partition
Check particular belong to which partition
The result for the above query is 5.
We can also use $Partition to check the partition information. Below query will display the row counts in each partition on the TransactionDate table.
To query the row from specific partition
Add another partition to the TransactionHistoryArchive table.
Select the range value , you will see the new range is been added.
Move TransactionDate partition2 (2003/9/1 – 2003/10/1) to TransactionHistoryArchive partition2.
From the TransactionHistory, there is no data in the Partition 1( < 2003/9/1) and no partition 2(2003/9/1 ~ 2003/10/1), We can merge those 2 partitions.
Les check the partition again
As you can see the partition boundary from 12 become 11 which means we only have 12 partitions now.
Lets add another partition to the end the TransactionHistory table.
As you can see, after the split, the new partition has been added.
In summery, merge will combine the 2 partitions. Split can split the partitions.
if you get the below error message during the split“
Msg 7707, Level 16, State 1, Line 1
The associated partition function 'TransactionArchivePF2' generates more partitions than there are file groups mentioned in the scheme 'TransactionArchivePS2'.”
The solution is try to Set the Next used [FILEGOUP] before you run the split
Partitioned Tables and Indexes in SQL Server 2005 http://msdn.microsoft.com/en-us/library/ms345146(v=sql.90).aspx