Partitions in SQL Server: Function Definitions, LEFT vs RIGHT

Partitions in SQL Server: Function Definitions, LEFT vs RIGHT

This is article 2 in my series of articles on partitions in SQL Server. In my first article Partitions in SQL Server: Creating a Partitioned Table, I briefly covered the difference between LEFT and RIGHT in partition function definitions. Here I have a comparison of the two.

The differences between left and right partition functions are:

  • LEFT: The partition will contain data equal to or less than the value specified. I.E. We define the partition by its’ highest value.
  • RIGHT: The partition will contain data equal to or greater than the value specified. I.E. We define the partition by its’ lowest value.

Testing Script

Below I create two sets of function, scheme and table. One with the function to the right, the other to the left. The script inserts a sample value to each then views the partition information to see where the row went.

Note that both tables have 4 partitions, and they appear to have the same lower and upper values. The only difference is how they are specified, and what happens to a value which is equal to that of the partition value.

--------------------------------------------------------------------------------
-- Partition Function: Split by year - RIGHT
--------------------------------------------------------------------------------
CREATE PARTITION FUNCTION [FactTable_R_PF]
      (DATE)
AS
RANGE
  RIGHT
FOR VALUES
(
    N'2016-01-01T00:00:00',
    N'2017-01-01T00:00:00',
    N'2018-01-01T00:00:00'
);

--------------------------------------------------------------------------------
-- Partition Scheme: Split by year - RIGHT
--------------------------------------------------------------------------------
CREATE PARTITION SCHEME [FactTable_R_PS]
AS PARTITION [FactTable_R_PF]
ALL TO ([primary])
;

--------------------------------------------------------------------------------
-- Create Table using RIGHT
--------------------------------------------------------------------------------
CREATE TABLE [dbo].[FactTable_partitioned_R]
(
    [TheDate]     DATE,
    [c_attribute] VARCHAR(100),
    [c_metric]    BIGINT,
    CONSTRAINT [PK_FactTable_partitioned_R] PRIMARY KEY ([TheDate], [c_attribute])
)
ON [FactTable_R_PS] ([TheDate])
WITH
( DATA_COMPRESSION = ROW
)
;

--------------------------------------------------------------------------------
-- Partition Function: Split by year - LEFT
--------------------------------------------------------------------------------
CREATE PARTITION FUNCTION [FactTable_L_PF]
      (DATE)
AS
RANGE
  LEFT
FOR VALUES
(
    N'2016-01-01T00:00:00',
    N'2017-01-01T00:00:00',
    N'2018-01-01T00:00:00'
);

--------------------------------------------------------------------------------
-- Partition Scheme: Split by year - LEFT
--------------------------------------------------------------------------------
CREATE PARTITION SCHEME [FactTable_L_PS]
AS PARTITION [FactTable_L_PF]
ALL TO ([primary])
;

--------------------------------------------------------------------------------
-- Create Table using LEFT
--------------------------------------------------------------------------------
CREATE TABLE [dbo].[FactTable_partitioned_L]
(
    [TheDate]     DATE,
    [c_attribute] VARCHAR(100),
    [c_metric]    BIGINT,
    CONSTRAINT [PK_FactTable_partitioned_L] PRIMARY KEY ([TheDate], [c_attribute])
)
ON [FactTable_L_PS] ([TheDate])
WITH
( DATA_COMPRESSION = ROW
)
;

--------------------------------------------------------------------------------
-- Sample data: '01-JAN-17'
--------------------------------------------------------------------------------
INSERT
  INTO [dbo].[FactTable_partitioned_R]
      ([TheDate], [c_attribute], [c_metric])
VALUES('01-JAN-17',      'Test',          1)
;
INSERT
  INTO [dbo].[FactTable_partitioned_L]
      ([TheDate], [c_attribute], [c_metric])
VALUES('01-JAN-17',      'Test',          1)
;

--------------------------------------------------------------------------------
-- Sample data: '01-JUN-17'
--------------------------------------------------------------------------------
/*
INSERT
  INTO [dbo].[FactTable_partitioned_R]
      ([TheDate], [c_attribute], [c_metric])
VALUES('01-JUN-17',      'Test',          1)
;
INSERT
  INTO [dbo].[FactTable_partitioned_L]
      ([TheDate], [c_attribute], [c_metric])
VALUES('01-JUN-17',      'Test',          1)
;
*/

GO

--------------------------------------------------------------------------------
-- View Usage
--------------------------------------------------------------------------------
SELECT OBJECT_NAME(p.[object_id]) AS [Table_Name],
       p.[partition_number],
       p.[rows]                   AS [Partition_rows],
       i.[name]                   AS [Index_Name],
       s.[name]                   AS [Scheme_Name],
       f.[name]                   AS [Function_Name],
       lv.[value]                 AS [Lower_Value],
       rv.[value]                 AS [Upper_Value]
  FROM sys.partitions p
    INNER JOIN sys.objects o
       ON o.[object_id] = p.[object_id]
     LEFT JOIN sys.indexes i
       ON i.[object_id] = p.[object_id]
      AND i.[index_id]  = p.[index_id]
     LEFT JOIN sys.partition_schemes s
       ON s.[data_space_id] = i.[data_space_id]
     LEFT JOIN sys.partition_functions f
       ON f.[function_id] = s.[function_id]
     LEFT JOIN sys.partition_range_values rv
       ON rv.[function_id] = f.[function_id]
      AND rv.[boundary_id] = p.[partition_number]
     LEFT JOIN sys.partition_range_values lv
       ON lv.[function_id] = f.[function_id]
      AND lv.[boundary_id] = p.[partition_number] - 1
WHERE 1=1
   AND o.[name] LIKE 'FactTable%'
ORDER BY o.[name],
       i.[name],
       p.[partition_number]
;

GO

--------------------------------------------------------------------------------
-- Clean up
--------------------------------------------------------------------------------

DROP TABLE [dbo].[FactTable_partitioned_R];
GO
DROP TABLE [dbo].[FactTable_partitioned_L];
GO
DROP PARTITION SCHEME [FactTable_R_PS];
GO
DROP PARTITION SCHEME [FactTable_L_PS];
GO
DROP PARTITION FUNCTION [FactTable_R_PF];
GO
DROP PARTITION FUNCTION [FactTable_L_PF];

Results

The code above includes two sets of inserts (one commented out) so that we can see which partitions contain the row inserted for each case. I’ve listed the output for the two cases below.

Jan 2017

Here we see the difference in which partition is used. For the left partitioned table partition 2 is used, for the right partition 3.

Table_Name              partition_number Partition_rows Index_Name                 Scheme_Name    Function_Name  Lower_Value             Upper_Value
----------------------- ---------------- -------------- -------------------------- -------------- -------------- ----------------------- -----------------------
FactTable_partitioned_L 1                0              PK_FactTable_partitioned_L FactTable_L_PS FactTable_L_PF NULL                    2016-01-01 00:00:00.000
FactTable_partitioned_L 2                1              PK_FactTable_partitioned_L FactTable_L_PS FactTable_L_PF 2016-01-01 00:00:00.000 2017-01-01 00:00:00.000 <--  LEFT '01-JAN-17'
FactTable_partitioned_L 3                0              PK_FactTable_partitioned_L FactTable_L_PS FactTable_L_PF 2017-01-01 00:00:00.000 2018-01-01 00:00:00.000
FactTable_partitioned_L 4                0              PK_FactTable_partitioned_L FactTable_L_PS FactTable_L_PF 2018-01-01 00:00:00.000 NULL
FactTable_partitioned_R 1                0              PK_FactTable_partitioned_R FactTable_R_PS FactTable_R_PF NULL                    2016-01-01 00:00:00.000
FactTable_partitioned_R 2                0              PK_FactTable_partitioned_R FactTable_R_PS FactTable_R_PF 2016-01-01 00:00:00.000 2017-01-01 00:00:00.000
FactTable_partitioned_R 3                1              PK_FactTable_partitioned_R FactTable_R_PS FactTable_R_PF 2017-01-01 00:00:00.000 2018-01-01 00:00:00.000 <-- RIGHT '01-JAN-17'
FactTable_partitioned_R 4                0              PK_FactTable_partitioned_R FactTable_R_PS FactTable_R_PF 2018-01-01 00:00:00.000 NULL

Jun 2017

In both cases, partition 3 is used. There appears to be no difference between the two definitions.

Table_Name              partition_number Partition_rows Index_Name                 Scheme_Name    Function_Name  Lower_Value             Upper_Value
----------------------- ---------------- -------------- -------------------------- -------------- -------------- ----------------------- -----------------------
FactTable_partitioned_L 1                0              PK_FactTable_partitioned_L FactTable_L_PS FactTable_L_PF NULL                    2016-01-01 00:00:00.000
FactTable_partitioned_L 2                0              PK_FactTable_partitioned_L FactTable_L_PS FactTable_L_PF 2016-01-01 00:00:00.000 2017-01-01 00:00:00.000
FactTable_partitioned_L 3                1              PK_FactTable_partitioned_L FactTable_L_PS FactTable_L_PF 2017-01-01 00:00:00.000 2018-01-01 00:00:00.000 <--  LEFT '01-JUN-17'
FactTable_partitioned_L 4                0              PK_FactTable_partitioned_L FactTable_L_PS FactTable_L_PF 2018-01-01 00:00:00.000 NULL
FactTable_partitioned_R 1                0              PK_FactTable_partitioned_R FactTable_R_PS FactTable_R_PF NULL                    2016-01-01 00:00:00.000
FactTable_partitioned_R 2                0              PK_FactTable_partitioned_R FactTable_R_PS FactTable_R_PF 2016-01-01 00:00:00.000 2017-01-01 00:00:00.000
FactTable_partitioned_R 3                1              PK_FactTable_partitioned_R FactTable_R_PS FactTable_R_PF 2017-01-01 00:00:00.000 2018-01-01 00:00:00.000 <-- RIGHT '01-JUN-17'
FactTable_partitioned_R 4                0              PK_FactTable_partitioned_R FactTable_R_PS FactTable_R_PF 2018-01-01 00:00:00.000 NULL

 

Leave a Reply

Your email address will not be published. Required fields are marked *

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