Skip to the page
Tecyfy

Azure interview question · hard

Dedicated SQL pool data distribution options (Hash, Round Robin, Replicate). How does the choice impact query performance for different workloads?

The short answerThe 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.

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 BY on 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

HashRound-robinReplicate
Use forLarge factsStaging, no good keySmall dimensions
JoinsLocal on the keyNeed movementLocal
Load speedGoodFastestSlower (copies)
Main riskSkew from a bad keyData movementRebuilds after changes

How to check your choice

  • DBCC PDW_SHOWSPACEUSED('dbo.FactOrders') shows rows per distribution, to spot skew.
  • EXPLAIN on a slow query shows SHUFFLE_MOVE or BROADCAST_MOVE steps, which mean the distribution doesn't fit the join.

Last updated 16 October 2026

Asked next