Impact of LEFT or RIGHT in SQL Server Partition Function's Definition

SQL Server ( since 2005 ) has introduced partitioning to effectively manage very large databases ( VLDBs) . 
Partitioning helps the DBA in 

a) maintaining the statistics at the partition level . 
b) easily load/unload  the data ( via sliding the partition ) 
c) optimal utilizing the storage for the datasets 
   (i.e., Current Year's Data can be housed in the faster storage ; archived data can be stored in cheaper / slower storage ) . 


Here are the steps to implement partitioning 

a) Define the partition function ( PF). 
   This step defines how the data should be partitioned . 
   The option of "RIGHT" or "LEFT" dictates how the upper boundary is treated by SQL Server. 
   
b) Define the partition schema ( PS) that uses the partition function created above
   This steps associates the physical storage ( aka filegroup) that corresponds to the partitions defined in the PF

c) Create a table that utilizes the PS  created above. 
  
In this blog post , I will show the differences between LEFT and RIGHT option definied in the PF. 
Rest of the partitioning 


Let us dive into a simple example . 

C:\>sqlcmd -W
1> use my2014db
2> go
Changed database context to 'my2014db'.

Step a) 
We will create a PF with LEFT range for three values ( 10 , 20 , 30) . 


1> Create Partition Function pf_seqno_l ( int) as range left for values ( 10 , 20 , 30 ) ;
2> go

This will create 
i) parition for any values less than or equal to 10 
ii) parition for any values greater than 10 and less than or equal to 20 
iii) parition for any values greater than 20 and less than or equal to 30 
iv) parition for any values greater than 30 

Step b) 
We will create a PS that will associate filegroup to the partitions . 
Here in this example , I have assigned ALL the paritions to go into PRIMARY filegroup; typically in production environments , it will be associated with multiple filegroups. 

1> Create Partition Scheme ps_seqno_l as partition pf_seqno_l ALL to ([PRIMARY]) ;
2> go
Partition scheme 'ps_seqno_l' has been created successfully. 'PRIMARY' is marked as the next used filegroup in partition scheme 'ps_seqno_l'.

Step c) 

We will create a table that will use the PS. 

1> Create table t_seqno_l
2> (   cid int identity ,
3>     seqno int )
4>      on ps_seqno_l(seqno) ;
5> go

Let us check , if the object does all the partitions . As we can see, it has 4 partitions . 

1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_l');
4> go

partition_number rows
---------------- ----
1 0
2 0
3 0
4 0

Let us insert a record and look at the partitions to confirm the number of records in the partitions . As the first record's value is 2 ( that <= 10 ) , that record is placed in the first partition. 


1> insert into t_seqno_l(seqno) values ( 2) ;
2> go

(1 rows affected)


1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_l');
4> go

partition_number rows
---------------- ----
1 1
2 0
3 0
4 0

(4 rows affected)

Second record is also placed in the first partition as the values is <= 10. 

1> insert into t_seqno_l(seqno) values ( 10) ;
2> go

(1 rows affected)
1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_l');
4> go
partition_number rows
---------------- ----
1 2
2 0
3 0
4 0

(4 rows affected)


Let us insert two more records. 

1> insert into t_seqno_l(seqno) values ( 20) ;
2> insert into t_seqno_l(seqno) values ( 50) ;
3> go

(1 rows affected)

(1 rows affected)

1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_l');
4> go
partition_number rows
---------------- ----
1 2
2 1
3 0
4 1

1> select * from t_seqno_l;
2> go
cid seqno
--- -----
3 2
4 10
5 20
6 50

(4 rows affected)

As you can see from the above , 
- Partition 1 has two records ( seqno with the value of 2 and 10) 
- Partition 2 has one record ( seqno with the value of 20) 
- Partition 3 has no record 
- Partition 4 has one record ( seqno with the value of 50 ; which is >= 30 ) 


Now , let us try PF with right option . 
Let us repeat the steps for creating PF , PS and the table. 

This will create 
i) partition for any values less than  10 
ii) partition for any values greater than or equal to  10 and less than  20 
iii) partition for any values greater than or equal to  20 and less than  30 
iv) partition for any values greater than or equal to  30 


1> Create Partition Function pf_seqno_r ( int) as range right for values ( 10 , 20 , 30 ) ;
2> go

1> Create Partition Scheme ps_seqno_r as partition pf_seqno_r ALL to ([PRIMARY]) ;
2> go
Partition scheme 'ps_seqno_r' has been created successfully. 'PRIMARY' is marked as the next used filegroup in partition scheme 'ps_seqno_r'.

1> Create table t_seqno_r
2> (   cid int identity ,
3>     seqno int )
4>      on ps_seqno_r(seqno) ;
5> go

1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_r');
4> go
partition_number rows
---------------- ----
1 0
2 0
3 0
4 0

(4 rows affected)

Let us insert a record. Let us insert a record and look at the partitions to confirm the number of records in the partitions . As the first record's value is 2 ( that <= 10 ) , that record is placed in the first partition. 

1> insert into t_seqno_r(seqno) values ( 2) ;
2> go

(1 rows affected)
1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_r');
4> go
partition_number rows
---------------- ----
1 1
2 0
3 0
4 0

(4 rows affected)

Let us second record . As you can see now , this record is placed in the second partition. Here the partition layout differ.  

1> insert into t_seqno_r(seqno) values ( 10) ;
2> go

(1 rows affected)
1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_r');
4> go
partition_number rows
---------------- ----
1 1
2 1
3 0
4 0

(4 rows affected)

1> insert into t_seqno_r(seqno) values ( 20) ;
2> insert into t_seqno_r(seqno) values ( 50) ;
3> go

(1 rows affected)

(1 rows affected)
1> SELECT partition_number , rows
2> FROM sys.partitions
3> WHERE OBJECT_ID = OBJECT_ID('t_seqno_r');
4> go
partition_number rows
---------------- ----
1 1
2 1
3 1
4 1


In short , LEFT or RIGHT dictates how the upper boundary  is placed with in the partitioned table. 

In the next few posts , we will discuss other features of partitioning. 

Thanks for reading.