May 9, 2016 at 2:50 pm
This is for a discussion I have been unable to locate from out here. It has been a few years since I first saw it, but I believe it was Gail Shaw who was saying something about using RANGE RIGHT instead of RANGE LEFT because of certain months during a year (which may have been specific to the year 2012 - that is when I last saw the article), because the months of May or June (or potentially both) would have issues with a RANGE LEFT for the values.
If anyone is familiar with the issue I am attempting to describe here, and could shed some light on it, I would appreciate the insight very much.
I am preparing a POC into table partitioning for a couple of tables in one of my databases (each of which has over 200 million rows, and take up a great deal of space).
Many thanks in advance. . .
May 9, 2016 at 3:21 pm
May 9, 2016 at 4:06 pm
That actually was the one that I found while looking for the one I was referring to here in my initial post. The mention from Gail I'm referring to was in a thread out here - not on another site.
I have tried and tried, but cannot find it. I just want to find out the PROS and CONS of the RANGE RIGHT or RANGE LEFT for the values, and to the specific point in this thread - whether there are some issues with certain months in the RANGE for the Partition Function, or if that was something in particular to the year 2012.
May 9, 2016 at 4:10 pm
You know what? I think I found it. It kind of looks like what she had to say, and is from 2012, but I specifically remember her saying something about May and June (But that may have just been her using those 2 months as an example). See the P.S. in her second response:
http://www.sqlservercentral.com/Forums/Topic1247696-391-1.aspx
May 9, 2016 at 4:42 pm
Or this thread you were in http://www.sqlservercentral.com/Forums/FindPost1292860.aspx 🙂
At any rate, as pointed out in both places, the main thing is that it's easier to get the function behaving the way you expect with datetimes if you use RANGE RIGHT.
With RANGE RIGHT, you're specifying the inclusive lower bound on each range. Whether you're partitioning by year or by month, the inclusive lower bound of each range is quite easy (it's just the 1st of the year or month, always, for every year and every month, whatever data type you're using).
With RANGE LEFT you're specifying the inclusive upper bound of each range, and that means that now you have to be a bit more careful, since the correct inclusive upper bound will depend on the datatype used and the month (if using datetime, you'd have to specify 23:59:59.997, and you'd have to be careful to use the 29th of February, not the 28th).
It's just a lot easier to work with RANGE RIGHT when you're talking about datetimes.
Cheers!
May 9, 2016 at 4:57 pm
Jacob Wilkins (5/9/2016)
Or this thread you were in http://www.sqlservercentral.com/Forums/FindPost1292860.aspx 🙂At any rate, as pointed out in both places, the main thing is that it's easier to get the function behaving the way you expect with datetimes if you use RANGE RIGHT.
With RANGE RIGHT, you're specifying the inclusive lower bound on each range. Whether you're partitioning by year or by month, the inclusive lower bound of each range is quite easy (it's just the 1st of the year or month, always, for every year and every month, whatever data type you're using).
With RANGE LEFT you're specifying the inclusive upper bound of each range, and that means that now you have to be a bit more careful, since the correct inclusive upper bound will depend on the datatype used and the month (if using datetime, you'd have to specify 23:59:59.997, and you'd have to be careful to use the 29th of February, not the 28th).
It's just a lot easier to work with RANGE RIGHT when you're talking about datetimes.
Cheers!
That's IT!!! 😀
Thank you!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply