Partition Alignment, LEFT or RIGHT?

While defining the Partition Range you need to specify if the Partition will be LEFT aligned or RIGHT aligned. Unless you have a good idea of this, it can be very confusing.
Let us break the code..... :)

A Partition Range defined with:
LEFT means Upper boundary of the 1st Partition Range
RIGHT means Lower boundary of the 2nd Partition Range

Also, the no. of Partition Boundary is always 1 less than total no. of Partition Range. e.g. A Partition with 5 Range will have 4 boundary specified.

Let us try and understand all with an example.
A Partition defined with the LEFT as follows
RANGE LEFT for VALUES ('20010101', '20020101', '20030101', '20040101')
will have the following Partition Range
<= 20010101
20010102 to 20020101
20020102 to 20030101
20030102 to 20040101
>= 20040102

(Notice the first date for every Partition Range is 1 more than the Boundary value specified as the LEFT is for Upper Boundary)

Similarly a Partition defined with the RIGHT
RANGE RIGHT for VALUES ('20010101', '20020101', '20030101', '20040101')
will have the following Partition Range
<20010101
20010101 to 20011231
20020101 to 20021231
20030101 to 20031231
>= 20040101

(Notice the first date for every Partition Range is the same as Boundary value specified as the RIGHT is for Lower Boundary)

Thus, you see it is more simple to specify a Partition Range as RIGHT Aligned for date values.

Specifying a Partition Boundary with a datetime data type has additional complexities. We will discuss this in a later post.