The full answer
The distribution decides where each row lives across the pool's 60 distributions, and therefore how much data must move between compute nodes when a query joins or aggregates. Hash suits large fact tables, replicate suits small dimensions, and round-robin suits staging tables or tables with no good key.
1. Hash distribution
- How it works: a hash of one column (or several) decides each row's distribution, so equal values always land together.
- Best for: large fact tables, and large tables joined to each other on the same key.
- Performance: joins and
GROUP BYon the distribution column run locally with no data movement. Joins on other columns need a shuffle. - Choosing the column: many distinct values, evenly spread (to avoid skew), used in joins, not usually filtered on, and not updated. A skewed key makes one distribution do most of the work and slows every query.
CREATE TABLE dbo.FactOrders
( OrderId BIGINT, CustomerId INT, OrderDate DATE, Amount DECIMAL(12,2) )
WITH (DISTRIBUTION = HASH(CustomerId), CLUSTERED COLUMNSTORE INDEX);
2. Round-robin distribution
- How it works: rows are spread evenly in turn, regardless of their values. It is the default when no distribution is given.
- Best for: staging tables, where load speed matters most, and tables with no suitable key.
- Performance: no skew and fast loads, but joins almost always need data movement, so it is a poor choice for large tables queried often.
CREATE TABLE stg.Orders
WITH (DISTRIBUTION = ROUND_ROBIN, HEAP)
AS SELECT * FROM ext.OrdersFiles;
3. Replicated tables
- How it works: a full copy of the table is kept on each compute node.
- Best for: small dimension tables, under about 2 GB compressed, that are joined often and change rarely.
- Performance: joins to them need no movement. After a change, the copies are rebuilt on the next query, so frequently updated tables lose the benefit.
CREATE TABLE dbo.DimDate
WITH (DISTRIBUTION = REPLICATE, CLUSTERED INDEX (DateKey))
AS SELECT * FROM stg.DimDate;
Summary
| Hash | Round-robin | Replicate | |
|---|---|---|---|
| Use for | Large facts | Staging, no good key | Small dimensions |
| Joins | Local on the key | Need movement | Local |
| Load speed | Good | Fastest | Slower (copies) |
| Main risk | Skew from a bad key | Data movement | Rebuilds after changes |
How to check your choice
DBCC PDW_SHOWSPACEUSED('dbo.FactOrders')shows rows per distribution, to spot skew.EXPLAINon a slow query showsSHUFFLE_MOVEorBROADCAST_MOVEsteps, which mean the distribution doesn't fit the join.
Last updated 16 October 2026
Sources