What it means range right and left in table partitioning?

As you know, the partition function defines how to partition data based on the partition column in Sql Server. It doesn’t explicitly define the partitions and which rows are placed in each partition.

The partition function determines boundary values, the values between partitions.

Partition Numbers = boundary values count + 1

However, left and right range topics sometimes are confused.

I want to show a few samples about left and right range. Then, at the end of the blog post I share a simple script of a partition table sample.

Fundamentally meaning:

1- RIGHT means < or >=

2- LEFT means <= and >.

Sample-1

CREATE PARTITION FUNCTION YearPartitions (date)
AS RANGE RIGHT FOR VALUES ( ‘2010–01–01’, ‘2015–01–01’,’2020–01–01')
GO

Sample-2

CREATE PARTITION FUNCTION ProductTypes (integer)
AS RANGE RIGHT FOR VALUES ( 1,2,3,4)
GO

Sample-3

CREATE PARTITION FUNCTION PF_Dictionary (nvarchar(100))
AS RANGE LEFT FOR VALUES ( ‘A’, ‘B’,’C’,’D’)
GO

Let’s create a partition sample with tsql.

use AdventureWorks2017
go
--- create partition function
CREATE PARTITION FUNCTION PF_Dictionary (nvarchar(100))
AS RANGE LEFT FOR VALUES ( ‘A’, ‘B’,’C’,’D’)
GO
--- create scheme
CREATE PARTITION SCHEME PS_Dictionary
AS PARTITION PF_Dictionary
ALL TO ([PRIMARY])
GO
 --- create table
create table Dictionary(
id int identity(1,1),
Word nvarchar(100),
Meaning nvarchar(100)
) on PS_Dictionary(Word)
--- add some data
insert into Dictionary values(‘book’,’kitap’),(‘car’,’araba’),(‘door’,’kapi’),(‘mouse’,’fare’),(‘apple’,’elma’)

Then, monitor partitions.

 ---Cathrine Wilhelmsen monitoring script
SELECT OBJECT_SCHEMA_NAME(pstats.object_id) AS SchemaNam
,OBJECT_NAME(pstats.object_id) AS TableName
,ps.name AS PartitionSchemeName
,ds.name AS PartitionFilegroupName
,pf.name AS PartitionFunctionName
,CASE pf.boundary_value_on_right WHEN 0 THEN ‘Range Left’ ELSE ‘Range Right’ END AS PartitionFunctionRange
,CASE pf.boundary_value_on_right WHEN 0 THEN ‘Upper Boundary’ ELSE ‘Lower Boundary’ END AS PartitionBoundary
,prv.value AS PartitionBoundaryValue
,c.name AS PartitionKey
,CASE
WHEN pf.boundary_value_on_right = 0
THEN c.name + ‘ > ‘ + CAST(ISNULL(LAG(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), ‘Infinity’) AS VARCHAR(100)) + ‘ and ‘ + c.name + ‘ <= ‘ + CAST(ISNULL(prv.value, ‘Infinity’) AS VARCHAR(100))
ELSE c.name + ‘ >= ‘ + CAST(ISNULL(prv.value, ‘Infinity’) AS VARCHAR(100)) + ‘ and ‘ + c.name + ‘ < ‘ + CAST(ISNULL(LEAD(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), ‘Infinity’) AS VARCHAR(100))END AS PartitionRange
,pstats.partition_number AS PartitionNumber
,pstats.row_count AS PartitionRowCount
,p.data_compression_desc AS DataCompression
FROM sys.dm_db_partition_stats AS pstats
INNER JOIN sys.partitions AS p ON pstats.partition_id = p.partition_id
INNER JOIN sys.destination_data_spaces AS dds ON pstats.partition_number = dds.destination_id
INNER JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
INNER JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
INNER JOIN sys.indexes AS i ON pstats.object_id = i.object_id AND pstats.index_id = i.index_id AND dds.partition_scheme_id = i.data_space_id AND i.type <= 1 /* Heap or Clustered Index */
INNER JOIN sys.index_columns AS ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.partition_ordinal > 0
INNER JOIN sys.columns AS c ON pstats.object_id = c.object_id AND ic.column_id = c.column_id
LEFT JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id AND pstats.partition_number = (CASE pf.boundary_value_on_right WHEN 0 THEN prv.boundary_id ELSE (prv.boundary_id+1) END)
WHERE pstats.object_id = OBJECT_ID(‘Dictionary’)
ORDER BY TableName, PartitionNumber;

You can add some data and monitor where the data are.

Sources:

https://www.cathrinewilhelmsen.net/2015/04/12/table-partitioning-in-sql-server/