PARTITION BY RANGE

PARTITION BY RANGE is used in partitioned tables and index partitioning to define how data should be distributed across partitions based on the values of a specific column.

How It Works:

• Range Partitioning: Data is divided into partitions based on a specified range of values. For example, you might partition data based on date ranges or numerical ranges.
• Partition Function: A partition function defines the ranges for the partitioning. It maps a range of values to a specific partition.
• Partition Scheme: A partition scheme defines how the partitions are stored across filegroups.

Example:

Suppose you want to partition a table based on the OrderDate column, with each partition containing data for a specific year.

Create a Partition Function:

This function defines partitions for dates before January 1st, 2022, January 1st, 2023, and January 1st, 2024.

CREATE PARTITION FUNCTION OrderDatePF (DATE)
AS RANGE LEFT FOR VALUES ('2022-01-01', '2023-01-01', '2024-01-01');

Create a Partition Scheme:

This scheme maps partitions to filegroups.

CREATE PARTITION SCHEME OrderDatePS
AS PARTITION OrderDatePF
TO ([PRIMARY], [FG2022], [FG2023], [FG2024]);

Create a Partitioned Table:

The table Orders is now partitioned based on the OrderDate column.

CREATE TABLE Orders
(
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    Amount DECIMAL(10, 2)
)
ON OrderDatePS (OrderDate);

Summary:

Defines how data is distributed into partitions based on ranges of values. Useful for managing large datasets by dividing them into more manageable chunks.