June 4, 2015 at 8:55 am
Hi Guys,
I'm looking to partition a big table into years.
I have a big table with data going back to 2001.
I need to partition this into file groups each for the last 5 years, and the rest into its own file group
I was going to convert the inherent date time field into the format int(yyyymmdd) and partition on that field.
Is this an acceptable way to achieve this? Is there a better way to partition on date? [not time]
Cheers
Alex
June 4, 2015 at 9:04 am
How about a computed (and persisted) column, set up to be YEAR(existingdatecolumn) ? That could be the basis of the partition function... Not sure that you even need the column, though. Look into Books Online (aka BOL) to see what the partition function needs.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 4, 2015 at 9:11 am
Please see the following on creating the partition function:
https://msdn.microsoft.com/en-us/library/ms187802.aspx
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 4, 2015 at 10:43 am
Brilliant Cheers!
I've now changed it to use and return a date type instead.
Though I now get this error?
Warning: Range value list for partition function 'pfnWeatherEventsByYear' is not sorted by value. Mapping of partitions to filegroups during CREATE PARTITION SCHEME will use the sorted boundary values if the function 'pfnWeatherEventsByYear' is referenced in CREATE PARTITION SCHEME.
I'm not sure what it means, can anyone shed some light, or point me to a doc?
Cheers
Alex
June 4, 2015 at 6:13 pm
Post your partition function.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply