Question 276 of 820
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 columns: OrderID (primary key, clustered), CustomerID, OrderDate, TotalAmount. Queries frequently filter on CustomerID and a range of OrderDate, and then sort the results by OrderDate in descending order. The queries also return the TotalAmount column. Which indexing strategy will most improve query performance for these operations?
⚠ Common exam trap
Test-takers frequently choose an index with the sort column first (Option B) or forget to include the non-key column (Option D), not realizing that covering indexes with the correct key order eliminate expensive key lookups and sorts.
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) with included column TotalAmount
It creates a covering index that matches the query's filter predicates (CustomerID equality, OrderDate range) and sort order (OrderDate DESC). By including TotalAmount as an included column, the index fully satisfies the query without needing to access the clustered index (key lookup), 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) with included column TotalAmount
Why this is correct
This index directly supports the filter on CustomerID and the range/order on OrderDate, and includes TotalAmount to avoid key lookups, making it the most efficient.
- ✗
Create a nonclustered index on (OrderDate DESC, CustomerID) with included column TotalAmount
Why it's wrong here
This index orders by date first, so for a specific CustomerID, the database would need to scan a potentially large range of dates before applying the CustomerID filter, which is less efficient.
- ✗
Change the clustered index to be on (CustomerID, OrderDate DESC)
Why it's wrong here
Changing the clustered index from the primary key OrderID could disrupt other queries and operations; also, a clustered index on these columns might cause page splits and fragmentation due to sequential inserts by OrderID.
- ✗
Create a nonclustered index on (OrderDate DESC) without including TotalAmount
Why it's wrong here
This index does not filter by CustomerID, so the query would need to scan many rows or perform a key lookup for each row, resulting in poor performance.
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.