July 10, 2012 at 11:50 am
I like to automate as much as I can when possible. And then I cam across this in a database I inherited:
CREATE PARTITION FUNCTION [pfTenDayRange](smalldatetime) AS RANGE RIGHT FOR VALUES (N'2012-06-25T00:00:00.000', N'2012-06-26T00:00:00.000', N'2012-06-27T00:00:00.000', N'2012-06-28T00:00:00.000', N'2012-06-29T00:00:00.000', N'2012-07-02T00:00:00.000', N'2012-07-03T00:00:00.000', N'2012-07-04T00:00:00.000', N'2012-07-05T00:00:00.000', N'2012-07-06T00:00:00.000', N'2012-07-09T00:00:00.000', N'2012-07-10T00:00:00.000', N'2012-07-11T00:00:00.000', N'2012-07-12T00:00:00.000', N'2012-07-13T00:00:00.000', N'2012-07-16T00:00:00.000', N'2012-07-17T00:00:00.000', N'2012-07-18T00:00:00.000', N'2012-07-19T00:00:00.000', N'2012-07-20T00:00:00.000', N'2012-07-23T00:00:00.000', N'2012-07-24T00:00:00.000', N'2012-07-25T00:00:00.000', N'2012-07-26T00:00:00.000', N'2012-07-27T00:00:00.000', N'2012-07-30T00:00:00.000', N'2012-07-31T00:00:00.000', N'2012-08-01T00:00:00.000', N'2012-08-02T00:00:00.000', N'2012-08-03T00:00:00.000', N'2012-08-06T00:00:00.000', N'2012-08-07T00:00:00.000', N'2012-08-08T00:00:00.000', N'2012-08-09T00:00:00.000', N'2012-08-10T00:00:00.000')
GO
There has got to be a way to automate the 10 business days I want to deal with other than hard coding the dates right? Can some sort of variable be used? Also, to make it more complicated, I need to preserve the last business day of the month in the tables.
Anyone have to deal with something like this that can educate me?
Aurora
July 10, 2012 at 11:54 am
My first question would be "Do you know why this is partitioned?" What's the reasoning?
Jared
CE - Microsoft
July 17, 2012 at 10:01 am
Hi Jared,
Thanks for taking a look. We currently partition the table because the database is extremely large (4tb) and in order for it to have better performance we purge out all the data except for the last 10 days. However, under new guidelines and rules the ast business day needs to be retained as well.
Aurora
July 17, 2012 at 10:15 am
Google "sliding window SQL Server partitioning" That should get you started. Then come back with any specific questions.
Jared
CE - Microsoft
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply