SQL Table Partitioning

Link to the video -https://www.youtube.com/watch?v=tQQ3XwrKbfM&t=32s

SQL Table Partition is needed to improve the performance if you have large amount of data.

Creating Partition

-- Create a partition function
CREATE PARTITION FUNCTION salesDateRangePF (DATE)
AS RANGE LEFT FOR VALUES ('2022-01-01', '2023-01-01');

-- Create a partition scheme
CREATE PARTITION SCHEME salesDateRangePS
AS PARTITION salesDateRangePF
TO ([PRIMARY], [PRIMARY], [PRIMARY]);

-- Create a partitioned table
CREATE TABLE sales (
    sale_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2),
    region VARCHAR(50)
) ON salesDateRangePS (sale_date);

Checking Existing Partition Functions

SELECT name AS PartitionFunctionName
FROM sys.partition_functions;

After Creating partition you will be able to see as shown below