Understanding Left vs. Right Partition Functions (with Diagrams)

You’re designing table partitioning, or you want to make a change to an existing partition function. It’s critical to understand the difference between how “left” and “right” partition functions behave, but the documentation is a bit confusing on this topic.

Honestly, even after years of working with partitioning, it’s easy to get confused about left and right after you think about something else for thirty seconds.

This is explained in Microsoft’s documentation with some tables. But for me, diagrams make this MUCH easier.

“Left” based partition functions use upper boundaries, and all boundaries are inclusive

This is not as much fun as an all-inclusive resort. But know that boundary points are always “included” with a partition.

Left based partitions are the default, if you don’t specify whether you want left or right. Here’s an example using a DATETIME2 data type with precision 7:

Left based partitioning can require being particularly nitpick-y if you’re working with the DATETIME or DATETIME2 data types. For example, if you’re partitioning by month, you typically don’t want the first moment of a month to be physically stored with data from the previous month– so you have to specify the last second of the last hour of the last day of the month at the boundary point. (Related fact: DATETIME is peculiar because it rounds microseconds to increments of 0, 3, and 7)

“Right” based partition functions use lower boundaries (also inclusive)

Right based partitions are simpler to define when it comes to DATETIME and DATE types, because every day begins at midnight and every month begins on the first.

Why do you have empty partitions in those diagrams?

It’s a best practice to keep the partitions at the ends of your table empty. This keeps your life simpler if you’re going to be adding (splitting) or removing (merging) partitions at the ends of your table.

Which is better, left or right?

Honestly, I don’t have a huge preference these days. Either of them can be a big old pain if you mess up your boundary points, or let data “spill” into a location it shouldn’t because you forgot to keep empty partitions at the end of the table, particularly if you’re using a complex filegroup setup.

So I recommend using whichever seems more straightforward for your data type and scenario. Just make sure that if you’re doing sliding window, or merging/splitting partitions for any reason, that you test all that code and make sure that you’re not accidentally causing data movement that will become painful when your data sizes grow.

Previous Post
Index Types: Heaps, Primary Keys, Clustered and Nonclustered Indexes (Dear SQL DBA Episode 28)
Next Post
Should I Learn Fulltext Indexing? (Dear SQL DBA Episode 29)

Related Posts

11 Comments. Leave new

  • […] 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. […]

    Reply
  • That makes much better sense – thanks Kendra

    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.

    Reply
  • 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.

    Reply
  • Sorry I forgot to mention that it is an INT datatype…

    Reply
    • 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)?

      Reply
  • 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.

    Reply
    • 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.

      Reply
  • 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.

    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.

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu