October 29, 2012 at 8:36 am
Unfortunately we have a very flat database(i.e. not normalized properly) and everything goes against one primary table. We also have no archival functionality and we are not in a position to redesigne the
database, so, I was thinking of partitioning the main table. I'm thinking the partitioning key could be the orderdate, however, I think it would be easier if I created a computed column that would derive the year based off of the orderdate and partition on that. My main question is would I have to have all of the queries modified to reflect on the computed year column?
The whole idea is to get the older data onto separate disk drives and place the current year on it's on disk drive improving performance.
Thanks in advance.
October 29, 2012 at 8:53 am
You can partition based on the Orderdate,
what you would do is Left Align the partition with the partition being set as 01-JAN-YYYY 00:00:00, this will then create stripes.
create partition function F_DatePartition (int)
as range left for values (
'01-jan-2010 00:00:00.000','01-jan-2011 00:00:00.000','01-jan-2012 00:00:00.000',
'01-jan-2013 00:00:00.000','01-jan-2020 00:00:00.000'
)
Go
CREATE PARTITION SCHEME [S_PeriodPartition] AS PARTITION [F_PeriodPartition]
TO ([PRIMARY], [Secondary], [Tertiary], [Quad],[Quinn]
Go
I'm not sure how to set up a table thats already inplace, as I tend to do this on new tables only.
You'd also need to set up a an annual job to create a moveing windows using the MERGE Partition functions.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply