I am working with a data-ware house and i want to partition the last three years of data into Hot data storage and any further than three years should be placed to cold. I want to be able to run a script monthly that would move data from Hot to Cold if data as gotten older I have currently Started by looking on line and have been testing the following script

Alter Database [Dev_PartionTest] Add Filegroup [HOT_DATA]       --------
Go                                                                  --------
Alter Database [Dev_PartionTest] Add Filegroup [COLD_DATA]       --------
Go                                                                  --------

Alter Database [Dev_PartionTest] Add FILE ( NAME = N'HOT_DATA', FILENAME = N'....\HOT_DATA.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB, MAXSIZE = 20000MB ) TO Filegroup [HOT_DATA]
Alter Database [Dev_PartionTest] Add FILE ( NAME = N'COLD_DATA', FILENAME = N'..\COLD_DATA.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB, MAXSIZE = 20000MB) TO Filegroup [COLD_DATA]

 Declare @Previousyear Date
 set @Previousyear = (SELECT DATEADD(year, -2, GETDATE()));

 Create Partition Function [MonthlyPartitionArchive] (datetime)
   as Range Right For Values (@Previousyear,  GETDATE())

Create Partition Scheme RangePartScheme as Partition 

 Create Clustered Index IDX_Part On Tester(created) 
 On RangePartScheme (created);

Could you guide me as to how i can develop such a a script.

    Create Partition Function [MonthlyPartitionArchive] (datetime)
   as Range Right For Values (@Previousyear,  GETDATE())

The issue i face is if i use the getdate to insert a record

 INSERT INTO Tester( Name, gender, created, description )
 VALUES ('Nicks', 'M',GETDATE(), 'Lloyds Office'), 

The getdate function would return date higher than the date time used in the function above to set the upper bounds on the right in the create partition function. how can i set the current year without having to type it in manually everytime. Can this script be running on a monthly process to update partition is that possible.

  • Why? Have you attempted something? What is the problem? You already pointed out the limitation is by the third year. – clifton_h May 25 '18 at 22:22
  • I have attempted something with a test. it correctly pointed the rows to the file group inserted a couple more records that had the current date but a couple seconds greate than the upper bounds and it was placed to the primary file group. what i know is how to make the most current data stay within the hot data file group and those greater than three years go to cold. – Anonymous Z May 25 '18 at 22:29
  • how do i not pass the upper bound would date be a better choice than oppose to datetime data type – Anonymous Z May 25 '18 at 22:30
  • I suppose you could always use a really small flag like a BIT and set that to 1 when it is Cold. There are a few simple ways. Also, put what you test and what you find is blocking a solution in the question, not the comment sections. – clifton_h May 25 '18 at 22:34

You want RANGE LEFT instead of RANGE RIGHT, and you only need a single boundary value at the three-year-mark.

Make the partition function like this:

Create Partition Function [MonthlyPartitionArchive] (datetime)
 as Range LEFT For Values (@Previousyear);

The scheme should then be:

Create Partition Scheme RangePartScheme as Partition [MonthlyPartitionArchive]
| improve this answer | |
  • Could you answer with a sample of what you mean? – Anonymous Z May 25 '18 at 22:46
  • I'm on mobile, so it's tedious, but I'm working on it! – Max Vernon May 25 '18 at 22:52
  • How would i know be able to monitor know that after the end of this year data would move to the relevant partition ? meaning that data would go to cold next year that should be move to cold – Anonymous Z May 25 '18 at 23:04
  • There is no way to create a dynamic partition function. You'll need to schedule a job to alter the partition function as needed. Look at this answer for details about how to automate it. – Max Vernon May 26 '18 at 0:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.