How to Create Table in Dedicated SQL Pool with Distribution Keys | Azure Synapse Analytics Tutorial for beginners and Advance users

How to Create Table in Dedicated SQL Pool with Distribution Keys | Azure Synapse Analytics

🔍 What is a Distribution Key?

In Azure Synapse Analytics' Dedicated SQL Pool, a distribution key is a column used to determine how table data is divided across 60 physical distributions (partitions). This enables parallel query execution using Massively Parallel Processing (MPP), improving performance for large datasets.

Choosing the correct distribution method and key helps reduce data movement and enhances performance, especially for joins and aggregations.

📦 Types of Distribution Methods

• HASH: Rows are assigned based on the hash of a specified column. Best for large fact tables with predictable joins.
• ROUND ROBIN: Rows are evenly and randomly spread across all distributions. Simple but not optimal for joins.
• REPLICATE: Entire table is copied to each compute node. Best for small dimension tables used in frequent joins.

🛠️ Steps to Create a Table with Distribution Key

✅ Step 1: Open Synapse Studio

• Go to your Synapse Workspace in Azure Portal.
• Launch Synapse Studio from the workspace overview.

✅ Step 2: Create New SQL Script

• In Synapse Studio, go to the Data tab.
• Right-click your Dedicated SQL Pool and choose New SQL Script → Empty script.

✅ Step 3: Use CREATE TABLE Statement with Distribution

Use the following syntax to create a distributed table:

CREATE TABLE dbo.SalesFact
(
    SalesID INT NOT NULL,
    CustomerID INT,
    ProductID INT,
    Quantity INT,
    SalesAmount DECIMAL(10,2)
)
WITH
(
    DISTRIBUTION = HASH(CustomerID),
    CLUSTERED COLUMNSTORE INDEX
);

Explanation:

• CustomerID is used as the distribution key to optimize joins with customer data.
• CLUSTERED COLUMNSTORE INDEX is used for efficient compression and analytic performance.

📌 Tips for Choosing Distribution Key

• Choose a column that is frequently used in joins and has high cardinality.
• Avoid columns with many repeated values (low cardinality), as this leads to data skew.
• Use REPLICATE for small lookup tables and ROUND ROBIN for staging/temporary tables.

📺 Watch the Video Tutorial

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