Hi Friends,
Today I am going to share some basics with respect to partitioning however it is very important to understand the key concepts.
Below is the example to better know the differences of left and right.
CREATE PARTITION FUNCTION salesYearPartitions (datetime) |
The partition key provided in the function is of type that will be the primary key in our partitioned table and partition ranges are based on this column. In our table this column is saleTime with a data type of DATETIME. The range defined may be RIGHT or LEFT. Here we have used a range RIGHT. As a rule of thumb ranges can be translated as
- RIGHT means < or >=
- LEFT can be translated as <= and >.
In our case we have used RIGHT with following values.
Range RIGHT translation | |
| Record with saleTime | Destination partition |
| < 2009-01-01 | DBForPartitioning_1 |
| >=2009-01-01 and < 2010-01-01 | DBForPartitioning_2 |
| >=2010-01-01 | DBForPartitioning_3 |
If we had used range LEFT then partitioning criteria would be as follows:
Range LEFT translation | |
| Record with saleTime | Destination partition |
| <= 2009-01-01 | DBForPartitioning_1 |
| >2009-01-01 and <= 2010-01-01 | DBForPartitioning_2 |
| > 2010-01-01 | DBForPartitioning_3 |
In my case I need to partition the data on monthly basis so I was little confused on whether to use Left or Right partition. Many said if we are using Date as the partition column then we should go with Right partition but I wanted to really understand why can't we make use of Left partition and how can it makes difference as a result I tested with Adventureworks2014DW database on order date.
There is a table by name FactResellerSales and I created a dummy table like below
select * into factsellers_test_10 from FactResellerSales
CREATE PARTITION FUNCTION pfPtTableDate_100 (DATETIME) AS RANGE RIGHT FOR VALUES ('20110101','20110201','20110301',
'20110401','20110501','20110601','20110701','20110801','20110901','20111001','20111101','20111201','20120101','20120201','20120301',
'20120401','20120501','20120601','20120701','20120801','20120901','20121001','20121101','20121201');
CREATE PARTITION SCHEME schPtTableDate_100 AS PARTITION pfPtTableDate_100 ALL TO ([PRIMARY]);
CREATE CLUSTERED INDEX IX_PartitionedTable_ID ON Factsellers_test_100 (orderdate) ON schPtTableDate_100(orderdate)
I purposefully created 24 partitions as this has leap year (February in 2012) so this is how the data looks
My question is how to achieve the same results using left partition. What changes needs to be done????
The Answer is simple if you understand the concepts in a range left partition function, all boundary values are upper boundaries, they are the last values in the partitions. If you partition by year, you use December 31st. If you partition by month, you use January 31st, February 28th / 29th, March 31st, April 30th and so on. Then our partition function should be like the below.
CREATE PARTITION FUNCTION pfPtTableDate_100 (DATETIME) AS RANGE LEFT FOR VALUES ('20101231','20110131','20110228','20110331',
'20110430','20110531','20110630','20110731','20110831','20110930','20111031','20111130','20111231','20120131','20120229','20120331',
'20120430','20120531','20120630','20120731','20120831','20120930','20121031','20121130','20121231');
And the results are like this and they are one at the same except for the last 2 partitions as there is one extra partition in left .
Hope this gives you better picture.
Comments