The short answer
In Azure Synapse Dedicated SQL Pool, every table must declare how its rows are mapped across the 60 underlying distributions. Choosing the correct strategy prevents network data movement during joins.
The three distribution options
- HASH: Synapse computes a hash value on a single designated column to assign each row to one of the 60 distributions. Rows with identical key values always land on the exact same distribution. Use this for large with more than 2 million rows that participate in frequent joins.
- ROUND_ROBIN: Rows are spread evenly across all 60 distributions in a circular sequence without inspecting column values. This is the default setting. It guarantees zero and provides the fastest write speeds. Use this for temporary staging tables before transformation.
- REPLICATED: A complete copy of the table is cached on every compute node in the cluster. When a query joins a fact table against a replicated , the join executes locally on each node with zero network .
-- Large fact table distributed on join key
CREATE TABLE dbo.fact_sales (
sale_id BIGINT,
customer_id BIGINT,
total_amount DECIMAL(18,2)
)
WITH (
DISTRIBUTION = HASH(customer_id),
CLUSTERED COLUMNSTORE INDEX
);
-- Small lookup dimension table copied everywhere
CREATE TABLE dbo.dim_order_status (
status_id INT,
status_name VARCHAR(50)
)
WITH (
DISTRIBUTION = REPLICATED
);Practical selection rule
Replicated tables should be under two gigabytes in compressed size and update infrequently, because writing to a replicated table requires invalidating and rebuilding caches across all compute nodes. Large fact tables should always be HASH distributed on their primary join key.