DP-900 Practice Question: Identify considerations for relational data on Azure
A company uses Azure SQL Database for an e-commerce platform. The 'Orders' table has millions of rows with columns OrderID (primary key), CustomerID, OrderDate, and TotalAmount. Queries often filter by CustomerID (equality) and OrderDate (range). Currently, these queries are slow. Which index should be created to improve performance?
⚠ Common exam trap
Watch out — candidates often choose a single-column index on OrderDate (Option C) thinking it covers the range filter, but they overlook that without CustomerID as the leading key, the index cannot efficiently narrow down to a specific customer, resulting in a full index scan instead of a seek.
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
✓
A nonclustered index on (CustomerID, OrderDate)
The query pattern filters by CustomerID (equality) and OrderDate (range). A composite nonclustered index on (CustomerID, OrderDate) allows SQL Database to seek directly to the matching CustomerID rows and then efficiently scan the ordered OrderDate range within that partition, avoiding a full table scan or key lookup. This index order leverages the index's B-tree structure for both equality and range predicates.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
A nonclustered index on OrderID
Why it's wrong here
OrderID is already the clustered index's key, so a separate nonclustered index on OrderID would be redundant for this query and would not supply CustomerID or OrderDate as key columns. The query predicate filters on CustomerID and OrderDate, not on OrderID, so this index cannot be used to locate the desired rows. The clustered index on OrderID remains useful for lookups by primary key but is irrelevant to the order-history style filter in the workload.
When this WOULD be correct
If the query frequently filters or sorts by OrderID (e.g., searching for a specific order) and the table is a heap (no clustered index), a nonclustered index on OrderID would improve performance.
- ✓
A nonclustered index on (CustomerID, OrderDate)
Why this is correct
This composite index covers the query predicate perfectly. CustomerID is the equality column, and OrderDate is the range column. The index allows the database engine to efficiently locate rows for a specific customer and then scan a small range of dates.
- ✗
A nonclustered index on OrderDate
Why it's wrong here
A nonclustered index on OrderDate alone would let the engine seek into the date range but cannot directly enforce the CustomerID equality filter. Every row matching the date range would require a lookup into the clustered index to verify CustomerID, and because the date range might include orders from many customers, this can turn into a large number of lookups. For an optimal seek, CustomerID should be the leading column so equality narrows the rows before OrderDate is used as the range.
When this WOULD be correct
This option would be correct for a question where queries filter only by OrderDate (e.g., range queries) and not by CustomerID, such as 'Find all orders placed in a specific date range'.
- ✗
A clustered index on CustomerID
Why it's wrong here
The table already has a clustered index on OrderID (the primary key). You cannot have more than one clustered index on a table. Rebuilding the clustered index on CustomerID would change the physical order of rows and may affect other queries.
When this WOULD be correct
If the question specified that the primary key is not clustered (e.g., a heap table) and queries frequently filter by CustomerID with equality conditions only (no range on OrderDate), then a clustered index on CustomerID could be beneficial to quickly locate all rows for a given customer.
Option-by-option analysis
Why each answer is right or wrong
Understanding why wrong answers are wrong — and when they would be correct — is what separates a 750 score from a 900. The DP-900 exam frequently reuses these exact scenarios with slightly different constraints.
✓A nonclustered index on (CustomerID, OrderDate)Correct answer▾
Why this is correct
This composite index covers the query predicate perfectly. CustomerID is the equality column, and OrderDate is the range column. The index allows the database engine to efficiently locate rows for a specific customer and then scan a small range of dates.
✗A nonclustered index on OrderIDWrong answer — click to see why▾
Why this is wrong here
OrderID is the primary key and likely already has a clustered index. Adding a nonclustered index on OrderID does not help queries filtering by CustomerID and OrderDate, as it does not cover those columns.
★ When this WOULD be the correct answer
If the query frequently filters or sorts by OrderID (e.g., searching for a specific order) and the table is a heap (no clustered index), a nonclustered index on OrderID would improve performance.
Why candidates choose this
Candidates may think indexing the primary key always speeds up queries, without considering that the query predicates are on different columns.
✗A nonclustered index on OrderDateWrong answer — click to see why▾
Why this is wrong here
An index on OrderDate alone would not efficiently support queries filtering by both CustomerID and OrderDate, as it cannot narrow down the search by CustomerID first, leading to unnecessary index scans.
★ When this WOULD be the correct answer
This option would be correct for a question where queries filter only by OrderDate (e.g., range queries) and not by CustomerID, such as 'Find all orders placed in a specific date range'.
Why candidates choose this
Candidates may think indexing the column used in the range filter (OrderDate) is sufficient, overlooking the need to also cover the equality filter (CustomerID) for optimal performance.
✗A clustered index on CustomerIDWrong answer — click to see why▾
Why this is wrong here
A clustered index on CustomerID would physically reorder the entire table by CustomerID, which is not the primary key. This could disrupt the existing primary key structure and may not efficiently support range queries on OrderDate, as the data would be sorted by CustomerID first.
★ When this WOULD be the correct answer
If the question specified that the primary key is not clustered (e.g., a heap table) and queries frequently filter by CustomerID with equality conditions only (no range on OrderDate), then a clustered index on CustomerID could be beneficial to quickly locate all rows for a given customer.
Why candidates choose this
Candidates may think that indexing the column used in equality filters (CustomerID) is always optimal, and clustering the table on that column seems like a logical way to physically organize data for fast lookups, overlooking the impact on range queries and primary key structure.
Analysis generated from the official DP-900blueprint and verified against question context. The “when correct” sections are what AI assistants cite when candidates ask “what’s the difference between these options?”
Go deeper
Related to this question
Learn chapter
Azure SQL Services
Key term
Table
A table is a structured collection of data organized into rows and columns, used in databases and spreadsheets to store and manage information efficiently.
Key term
Index
An index is a data structure that speeds up data retrieval operations on a database table or file, much like a book index helps you find topics quickly.
About these practice questions
This DP-900 question is part of Courseiva's 820-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-900 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-900 exam.