Tag Archives: partitioning

Understanding Range Partitioning in SQL Server

Leave a reply

In SQL Server, partitioning divides large tables or indexes into smaller, more manageable pieces while still maintaining a single logical entity. Partitioning can be based on range, list, or other types of partitioning functions, but SQL Server supports range partitioning most commonly. Below, I’ll explain the different partition types and how to create them with commands.

Types of Partitioning in SQL Server

  1. Range Partitioning
    • RANGE LEFT: In this type, the partition function includes the boundary value in the left partition.
    • RANGE RIGHT: In this type, the partition function includes the boundary value in the right partition.
    These two types allow you to define partition boundaries based on ranges of values (e.g., date ranges, numeric ranges).

1. RANGE LEFT Partitioning

In RANGE LEFT partitioning, the boundary value belongs to the left partition. For example, if you set a boundary at '2021-12-31', then rows with values less than or equal to '2021-12-31' will be in the same partition.

Steps to Create RANGE LEFT Partitioning

Step 1: Create a Partition Function

CREATE PARTITION FUNCTION [MyRangeLeftPartitionFunction](DATE)
AS RANGE LEFT FOR VALUES ('2020-12-31', '2021-12-31', '2022-12-31');

This creates a partition function where:

  • Partition 1: <= '2020-12-31'
  • Partition 2: <= '2021-12-31'
  • Partition 3: <= '2022-12-31'
  • Partition 4: All values greater than 2022-12-31

Step 2: Create a Partition Scheme

CREATE PARTITION SCHEME [MyRangeLeftPartitionScheme]
AS PARTITION [MyRangeLeftPartitionFunction]
TO ([FileGroup1], [FileGroup2], [FileGroup3], [PRIMARY]);

This maps partitions to specific filegroups. The final partition (for values greater than 2022-12-31) will be stored on the PRIMARY filegroup.

Step 3: Create a Partitioned Table

CREATE TABLE [SalesOrders] (
    [OrderID] INT PRIMARY KEY,
    [OrderDate] DATE,
    [OrderAmount] DECIMAL(10, 2)
) ON [MyRangeLeftPartitionScheme]([OrderDate]);

This creates a table partitioned based on the OrderDate, with partitions defined by the RANGE LEFT scheme.

2. RANGE RIGHT Partitioning

In RANGE RIGHT partitioning, the boundary value belongs to the right partition. For example, if you set a boundary at '2021-12-31', then rows with values equal to '2021-12-31' and greater will be in the right partition.

Steps to Create RANGE RIGHT Partitioning

Step 1: Create a Partition Function

CREATE PARTITION FUNCTION [MyRangeRightPartitionFunction](DATE)
AS RANGE RIGHT FOR VALUES ('2020-12-31', '2021-12-31', '2022-12-31');

This creates a partition function where:

  • Partition 1: < '2020-12-31'
  • Partition 2: Between '2020-12-31' and '2021-12-31'
  • Partition 3: Between '2021-12-31' and '2022-12-31'
  • Partition 4: All values greater than '2022-12-31'

Step 2: Create a Partition Scheme

CREATE PARTITION SCHEME [MyRangeRightPartitionScheme]
AS PARTITION [MyRangeRightPartitionFunction]
TO ([FileGroup1], [FileGroup2], [FileGroup3], [PRIMARY]);

This partition scheme maps the ranges defined by the partition function to the filegroups.

Step 3: Create a Partitioned Table

CREATE TABLE [SalesOrders] (
    [OrderID] INT PRIMARY KEY,
    [OrderDate] DATE,
    [OrderAmount] DECIMAL(10, 2)
) ON [MyRangeRightPartitionScheme]([OrderDate]);