Question 241 of 820
DP-900 Practice Question: Identify considerations for relational data on Azure
An e-commerce company uses Azure SQL Database for order processing. The Orders table has columns: OrderID (unique, clustered index), CustomerID, OrderDate, Status, TotalAmount. A common query filters on CustomerID and OrderDate, and sorts by OrderDate descending. The query also returns TotalAmount. Which indexing strategy will produce the best query performance?
⚠ Common exam trap
The trap here is that candidates often focus on including all columns in the INCLUDE clause but fail to order the key columns correctly to support both the equality filter and the sort order, leading them to pick options that start with the sort column instead of the equality column.
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
✓
Create a nonclustered index on (CustomerID, OrderDate DESC) INCLUDE (TotalAmount)
It creates a covering index that supports both the equality filter on CustomerID and the range/sort on OrderDate DESC. By including TotalAmount as an included column, the query can be satisfied entirely from the nonclustered index without key lookups to the clustered index, minimizing I/O and improving performance.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Create a nonclustered index on (CustomerID, OrderDate DESC) INCLUDE (TotalAmount)
Why this is correct
This composite index supports the exact filter (CustomerID and OrderDate), the sort order (OrderDate DESC is included in the key), and the included TotalAmount column eliminates key lookups, making it a covering index for the query.
- ✗
Create a nonclustered index on (OrderDate) INCLUDE (CustomerID, TotalAmount)
Why it's wrong here
This index puts OrderDate first, but the query filters on CustomerID first; filtering on CustomerID would require scanning many date ranges for each CustomerID, which is less efficient than having CustomerID as the leading key.
- ✗
Create a nonclustered index on (OrderDate DESC) INCLUDE (CustomerID, TotalAmount)
Why it's wrong here
Placing OrderDate as the leading key column means the index is organized by date, not by customer, so a query that filters on CustomerID cannot perform a seek; instead, SQL Server must scan all date ranges and then apply a residual predicate to isolate that customer's rows. Although OrderDate DESC correctly matches the query's sort requirement, that benefit is moot when the leading column cannot be used for equality filtering on CustomerID. This approach is especially costly for large fact tables with many orders per date, and the included CustomerID/TotalAmount columns only help avoid key lookups, not the underlying scan.
- ✗
Create a nonclustered index on (CustomerID) INCLUDE (OrderDate, TotalAmount)
Why it's wrong here
This index has CustomerID as the leading key, but OrderDate is only included, not a key column. The query also sorts by OrderDate, which the index cannot provide efficiently because OrderDate is not part of the key. The sort would require a separate sort operation.
About these practice questions
Courseiva creates original exam-style practice questions with explanations and wrong-answer analysis. It does not publish real exam questions, exam dumps, or protected exam content. Learn why practice questions differ from exam dumps →
Last reviewed: Jun 11, 2026
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.
Question Discussion
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
Sign in to join the discussion.