DP-203 Design and implement data storage Practice Question
You are migrating an on-premises SQL Server database to Azure Synapse Analytics dedicated SQL pool. The database includes a table with 500 million rows that is frequently queried by date range. Which distribution strategy should you use for this table?
⚠ Common exam trap
Test-takers frequently choose round-robin distribution for large tables thinking it is simple and balanced, but they overlook that frequent date-range queries require data collocation and partition elimination, which only hash distribution on the query filter column can provide.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Hash distribution on the date column.
Hash distribution on the date column is correct because it distributes rows evenly across distributions based on a hash of the date value, enabling partition elimination and collocated joins when queries filter by date range. This strategy optimizes performance for the frequent date-range queries by ensuring that data for a specific date range is likely stored on the same distribution, minimizing data movement.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Hash distribution on the date column.
Why this is correct
Hash distribution on the date column allows partition elimination and efficient querying by date range.
- ✗
Hash distribution on an identity column.
Why it's wrong here
Hash on identity column distributes evenly but does not help date range queries.
- ✗
Replicated distribution.
Why it's wrong here
Replicated tables are for small tables; 500 million rows is too large.
- ✗
Round-robin distribution.
Why it's wrong here
Round-robin leads to data shuffling on queries, impacting performance.
Go deeper
Related to this question
About these practice questions
This DP-203 question is part of Courseiva's 760-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-203 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-203 exam.