Welcome To TechBrothersIT

Table Partitioning in Azure Synapse Dedicated SQL Pool | Concepts & Demo

📘 What is Table Partitioning?

Table partitioning in Azure Synapse Dedicated SQL Pool is a powerful technique used to divide a large table into smaller, more manageable parts (called partitions) based on a partition key — usually a date column. This helps optimize performance and simplify maintenance tasks.

🔍 Why Use Table Partitioning?

Performance Boost (Partition Elimination): Only relevant partitions are scanned during queries, reducing IO and speeding up results.
Faster Data Management: Load or remove data by entire partition instead of row-by-row.
Maintenance Efficiency: Index rebuilds can be scoped to specific partitions.

📐 RANGE RIGHT vs. RANGE LEFT

Partitioning uses boundary values. With RANGE RIGHT (used in this demo), the boundary value belongs to the higher partition.

PARTITION BY RANGE RIGHT FOR VALUES (20200101, 20210101, 20220101)

Partition	Data Range
1	OrderDateKey ≤ 20200101
2	20200101 < OrderDateKey ≤ 20210101
3	20210101 < OrderDateKey ≤ 20220101
4	OrderDateKey > 20220101

🛠️ Demo Script Highlights

✅ 1. Create Partitioned Table

CREATE TABLE dbo.SalesFact_Partitioned
(
    SaleID INT,
    ProductID INT,
    Quantity INT,
    SaleDate DATE,
    OrderDateKey INT,
    CustomerID INT
)
WITH
(
    DISTRIBUTION = HASH(CustomerID),
    CLUSTERED COLUMNSTORE INDEX,
    PARTITION (OrderDateKey RANGE RIGHT FOR VALUES (
        20200101, 20210101, 20220101, 20230101, 20240101
    ))
);

✅ 2. Insert Sample Data

INSERT INTO dbo.SalesFact_Partitioned (...) VALUES (..., 20200101, ...); -- Goes to Partition 1
INSERT INTO dbo.SalesFact_Partitioned (...) VALUES (..., 20200715, ...); -- Partition 2
-- Repeat for partitions 3–5

✅ 3. Create Identical Staging Table

CREATE TABLE dbo.SalesFact_Stage (...) 
WITH (
    DISTRIBUTION = HASH(CustomerID),
    CLUSTERED COLUMNSTORE INDEX,
    PARTITION (OrderDateKey RANGE RIGHT FOR VALUES (
        20200101, 20210101, 20220101, 20230101, 20240101
    ))
);

✅ 4. Switch Partition from Stage to Main Table

This is an instant metadata operation (very fast):

ALTER TABLE dbo.SalesFact_Stage
SWITCH PARTITION 2 TO dbo.SalesFact_Partitioned PARTITION 2 WITH (TRUNCATE_TARGET = ON);

📊 Check Row Count Per Partition

SELECT row_count, partition_nmbr 
FROM dbo.vTableSizes 
WHERE table_name = 'SalesFact_Partitioned' AND row_count > 0;

📌 Notes & Best Practices

Staging and target tables must match exactly (schema, index, distribution, partition)
Use partition switching for bulk loading and archiving scenarios
Filter by partitioning column to enable partition elimination in queries

📺 Watch the Video Tutorial

📚 Credit: Content created with the help of ChatGPT and Gemini.