Friday Reading 2017-02-10 | The DBA Who Came In From The Cold
February 10, 2017 1:01 am
[…] Understanding Left vs. Right Partition Functions (with Diagrams) Ever had your head slightly melted when trying to figure out partition boundaries? Kendra Little’s post has diagrams to make it (a little bit) easier. […]
Loading...
Reply
Tom Hamilton
February 11, 2017 3:54 am
That makes much better sense – thanks Kendra
Loading...
Reply
Benjamin Mathew
March 7, 2018 12:24 pm
Thanks Kendra. I like to remember it through the syntax of the partition function creation. When I think just right vs. left, I think of boundaries, i.e. right should mean that (on a number line) I am specifying the rightmost boundary for the partition – that’s the opposite of what it is! But when I think RANGE RIGHT vs. RANGE LEFT, I remember that I am specifying a boundary point, and the partition will range in that direction.
Loading...
Reply
Kendra Little
March 10, 2018 10:49 am
Ah, so “[the] RANGE [is to the] LEFT”
I like that!
Loading...
Reply
John
March 18, 2018 4:00 pm
Thanks for sharing your comments Kendra. My quick question is that if you are partitioning a table with 2 billion records, would you use LEFT or RIGHT? This is a static data that does not change at all, it is only used for analytics.
Loading...
Reply
John
March 18, 2018 4:02 pm
Sorry I forgot to mention that it is an INT datatype…
Loading...
Reply
Kendra Little
March 19, 2018 7:35 am
Are you planning to use any sort of rolling window — adding partitions, splitting / merging partitions? If so, will they be new high ints, or will the by “low” ints (or something else)?
Loading...
Reply
John
March 25, 2018 11:15 am
Thanks Kendra for your prompt response. There will not be any rolling windows at all and there will not be any new high or low ints in my case.
Loading...
Reply
Kendra Little
March 26, 2018 9:58 am
Thanks. I thought you might mean static simply for existing data, as in “no updates” (but there might be inserts). It’d be a coin toss for me in that case, they will both work well. You could look around and see if there’s any other partitioning in the environment and match whether it is right or left to be consistent. Otherwise just pick whichever feels more comfortable to you.
Loading...
Reply
John
March 27, 2018 9:57 am
Thanks so much for the confirmation Kendra! A big fun and long time follower! Love your art! I look forward to see how you maybe able to incorporated your IT experience into your artwork ;)! Just curious.
Loading...
Reply
Mike Williams
June 4, 2019 2:23 pm
I know this post is over a year old, but this article is good and concise. I think, though, it could be made a lot better if t-sql statements for the create partition function (and scheme) were included with each LEFT and RIGHT explanation. This would clearly illustrate which values are used to define the boundaries when defining partition function as either LEFT or RIGHT. Great work, though, as always! Thanks.
Loading...
Reply
GS SS
September 6, 2020 5:25 pm
I’ve an unique requirement for finding min / max value from a partition’s range values.
for e.g. I’ve a table partitioned on business date. when I do a min / max / distinct count of business date on the table – the result does not match when I try to apply the same logic on sql server’s partition range system table.
I would like to know why and if this is achievable.
Thanks in advance.
Loading...
Reply
Kendra Little
October 13, 2020 10:53 am
When you query the table itself, you’re getting the min / max of the data in the table.
When you query sys.partition_range_values, you are getting the logical boundary points that partition the data — but there may not be data in the table itself that aligns with the boundary points.
An analogy: on a highway there may be cars at many different locations. If the locations are the data, then the “mile markers” are the boundary points. Cars may or may not be at the exact location of the mile markers.
migrating comments from WordpressFriday Reading 2017-02-10 | The DBA Who Came In From The Cold February 10, 2017 1:01 am […] Understanding Left vs. Right Partition Functions (with Diagrams) Ever had your head slightly melted when trying to figure out partition boundaries? Kendra Little’s post has diagrams to make it (a little bit) easier. […]
Loading...
Reply Tom Hamilton February 11, 2017 3:54 am That makes much better sense – thanks Kendra
Loading... Reply Benjamin Mathew March 7, 2018 12:24 pm Thanks Kendra. I like to remember it through the syntax of the partition function creation. When I think just right vs. left, I think of boundaries, i.e. right should mean that (on a number line) I am specifying the rightmost boundary for the partition – that’s the opposite of what it is! But when I think RANGE RIGHT vs. RANGE LEFT, I remember that I am specifying a boundary point, and the partition will range in that direction.
Loading... Reply Kendra Little March 10, 2018 10:49 am Ah, so “[the] RANGE [is to the] LEFT”
I like that!
Loading...
Reply John March 18, 2018 4:00 pm Thanks for sharing your comments Kendra. My quick question is that if you are partitioning a table with 2 billion records, would you use LEFT or RIGHT? This is a static data that does not change at all, it is only used for analytics.
Loading... Reply John March 18, 2018 4:02 pm Sorry I forgot to mention that it is an INT datatype…
Loading... Reply Kendra Little March 19, 2018 7:35 am Are you planning to use any sort of rolling window — adding partitions, splitting / merging partitions? If so, will they be new high ints, or will the by “low” ints (or something else)?
Loading... Reply John March 25, 2018 11:15 am Thanks Kendra for your prompt response. There will not be any rolling windows at all and there will not be any new high or low ints in my case.
Loading... Reply Kendra Little March 26, 2018 9:58 am Thanks. I thought you might mean static simply for existing data, as in “no updates” (but there might be inserts). It’d be a coin toss for me in that case, they will both work well. You could look around and see if there’s any other partitioning in the environment and match whether it is right or left to be consistent. Otherwise just pick whichever feels more comfortable to you.
Loading... Reply John March 27, 2018 9:57 am Thanks so much for the confirmation Kendra! A big fun and long time follower! Love your art! I look forward to see how you maybe able to incorporated your IT experience into your artwork ;)! Just curious.
Loading... Reply Mike Williams June 4, 2019 2:23 pm I know this post is over a year old, but this article is good and concise. I think, though, it could be made a lot better if t-sql statements for the create partition function (and scheme) were included with each LEFT and RIGHT explanation. This would clearly illustrate which values are used to define the boundaries when defining partition function as either LEFT or RIGHT. Great work, though, as always! Thanks.
Loading... Reply GS SS September 6, 2020 5:25 pm I’ve an unique requirement for finding min / max value from a partition’s range values.
for e.g. I’ve a table partitioned on business date. when I do a min / max / distinct count of business date on the table – the result does not match when I try to apply the same logic on sql server’s partition range system table.
I would like to know why and if this is achievable.
Thanks in advance.
Loading... Reply Kendra Little October 13, 2020 10:53 am When you query the table itself, you’re getting the min / max of the data in the table.
When you query sys.partition_range_values, you are getting the logical boundary points that partition the data — but there may not be data in the table itself that aligns with the boundary points.
An analogy: on a highway there may be cars at many different locations. If the locations are the data, then the “mile markers” are the boundary points. Cars may or may not be at the exact location of the mile markers.
Loading...