August 7, 2008 at 10:12 am
I have a partition scheme as below:
CREATE PARTITION SCHEME Partition_Scheme_Data as PARTITION [Partition_Fn] TO ( [ACTIVE_MONTH_FG], [Archive_Aug_08], [Archive_File_Sep_08], [Archive_File_Oct_08])
The ACTIVE_MONTH_FG contains the current month's data (Aug 2008)
What I want to do is, on Sep 08, the data in [ACTIVE_MONTH_FG] that contains the Aug 2008 data should move to [Archive_File_Aug_08] and the data for the current month (which will be Sep 08) to come to the partition " [ACTIVE_MONTH_FG]".
Again on Oct 08, the data in [ACTIVE_MONTH_FG] that contains the Sep 2008 data should move to [Archive_File_Sep_08] and the data for the current month (which will be Oct 08) to come to the partition " [ACTIVE_MONTH_FG]".
I can write a automated job to do this.. but the fundamental question is.. Is it possible to change the partition function and schemes after they are created?. The goal is, the drives holding the files in the "ACTIVE_MONTH_FG" are faster and peforms better than the drives that holds the "Archive_" files.. So, by having the current month's data in a drive that performs better, I have a feeling that the application will perform better since all the queries will be mainly for the current month..
Thanks for any help..
August 7, 2008 at 10:27 am
What you're looking for is a "sliding window" scenario for dealing with partitioned data. Check out the following article...
http://msdn.microsoft.com/en-us/library/aa964122.aspx
Optionally you can get creative and manage it like the following...
http://blogs.msdn.com/menzos/archive/2008/06/30/table-partitioning-sliding-window-case.aspx
The article describes a fantastic method for using stored procs to manage a sliding partition window scenario.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply