DP-900 Practice Question: Identify considerations for relational data on Azure
A company uses Azure SQL Database for an order management system. The Orders table has columns: OrderID (int, primary key), CustomerID (int), OrderDate (datetime), Status (varchar), TotalAmount (decimal). Queries frequently filter on CustomerID and OrderDate to find orders from a specific customer within a date range. Which index would most improve performance for these queries?
⚠ Common exam trap
The trap here is that candidates often pick an index starting with OrderDate (Option D) because they think date-range filtering is the primary need, forgetting that the equality filter on CustomerID must be the leading column for an efficient 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 non-clustered index on (CustomerID, OrderDate) INCLUDE (TotalAmount)
The query filters on CustomerID and OrderDate, so a composite non-clustered index on (CustomerID, OrderDate) allows SQL Server to perform an index seek on both columns, drastically reducing the number of rows scanned. Including TotalAmount as a non-key column makes this a covering index, meaning all needed data (including TotalAmount) is in the index leaf pages, avoiding costly key lookups to the clustered index.
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 clustered index on OrderID
Why it's wrong here
A clustered index on OrderID physically sorts the entire table by OrderID, which is ideal for lookups and joins on that primary key column. However, it does not help this Order-management query because it provides no efficient path to locate rows by CustomerID and OrderDate; with no secondary index on those columns, the query engine would be forced to scan every order row, and the clustered index's key order is simply irrelevant to the filtering requirements.
When this WOULD be correct
If the query frequently searched for a specific order by OrderID (e.g., SELECT * FROM Orders WHERE OrderID = ?), then a clustered index on OrderID would be optimal for that point lookup.
- ✗
A non-clustered index on Status
Why it's wrong here
A non-clustered index on Status alone would only accelerate queries with predicates on Status, but the reported query filters on CustomerID and OrderDate. Since Status typically has very few distinct values (low cardinality), even if the optimizer chose a seek it would retrieve a large fraction of the table and then require costly key lookups to fetch TotalAmount, making it far less efficient than a covering composite index on the actual filter columns.
- ✓
A non-clustered index on (CustomerID, OrderDate) INCLUDE (TotalAmount)
Why this is correct
This composite non-clustered index is optimal because CustomerID is the leftmost key column, enabling a seek on CustomerID and then matching the OrderDate range or equality, while TotalAmount is stored as a non-key included column. That makes the index covering for this query, meaning all required data is present in the index pages and no round-trip to the clustered base table is needed, thereby minimizing logical reads and I/O.
- ✗
A non-clustered index on (OrderDate, TotalAmount)
Why it's wrong here
Placing OrderDate as the leading column instead of CustomerID prevents the optimizer from performing a single seek on CustomerID, which is the most selective filter in the query; it would instead scan or seek on OrderDate and then apply a residual predicate on CustomerID. Additionally, TotalAmount is included as a key column rather than in the INCLUDE clause, which unnecessarily increases the index size and does not contribute to fulfilling either the equality or range predicates for CustomerID and OrderDate.
When this WOULD be correct
If the query filters only on OrderDate and TotalAmount (e.g., finding orders with a total amount above a threshold within a date range), this index would be optimal as it directly supports both filter and potential covering.
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 non-clustered index on (CustomerID, OrderDate) INCLUDE (TotalAmount)Correct answer▾
Why this is correct
This composite non-clustered index is optimal because CustomerID is the leftmost key column, enabling a seek on CustomerID and then matching the OrderDate range or equality, while TotalAmount is stored as a non-key included column. That makes the index covering for this query, meaning all required data is present in the index pages and no round-trip to the clustered base table is needed, thereby minimizing logical reads and I/O.
✗A clustered index on OrderIDWrong answer — click to see why▾
Why this is wrong here
A clustered index on OrderID is already the primary key, so it exists by default. The query filters on CustomerID and OrderDate, not OrderID, so this index does not help with those filters.
★ When this WOULD be the correct answer
If the query frequently searched for a specific order by OrderID (e.g., SELECT * FROM Orders WHERE OrderID = ?), then a clustered index on OrderID would be optimal for that point lookup.
Why candidates choose this
Candidates may think a clustered index is always the best for performance, or they confuse the primary key index with being useful for all queries, not realizing it only helps when filtering on the key column.
✗A non-clustered index on (OrderDate, TotalAmount)Wrong answer — click to see why▾
Why this is wrong here
The index on (OrderDate, TotalAmount) does not include CustomerID, which is a primary filter in the queries. Without CustomerID as a leading key, the index cannot efficiently narrow down rows for a specific customer, leading to scans rather than seeks.
★ When this WOULD be the correct answer
If the query filters only on OrderDate and TotalAmount (e.g., finding orders with a total amount above a threshold within a date range), this index would be optimal as it directly supports both filter and potential covering.
Why candidates choose this
Candidates may think any index on columns used in WHERE clause helps, but they overlook the importance of leading column order for multi-column filters, especially when one column (CustomerID) is highly selective.
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
Data Roles and Core Concepts
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
Azure SQL Database
Azure SQL Database is a fully managed relational database-as-a-service (DBaaS) in Microsoft Azure, based on the SQL Server engine, that handles scaling, backups, patching, and high availability automatically.
About these practice questions
Courseiva writes every DP-900 question from scratch — 820 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or 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.