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
A composite nonclustered index keyed on CustomerID then OrderDate DESC satisfies the equality predicate, supports the descending range scan without a sort, and covering TotalAmount via INCLUDE avoids key lookups back to the clustered index, eliminating the residual lookup per row.
- ✗
Create a nonclustered index on (OrderDate DESC, CustomerID) with included column TotalAmount
Why it's wrong here
Leading with OrderDate DESC prevents an efficient seek on CustomerID, so the engine scans or seeks poorly for each customer's rows before sorting. A nonclustered index is correct when the equality predicate leads, followed by the range and sort column, with TotalAmount included to cover the query.
- ✗
Change the clustered index to be on (CustomerID, OrderDate DESC)
Why it's wrong here
Replacing the clustered index on the primary key reorders the table physically by CustomerID and OrderDate, which does not help queries that also return TotalAmount without a covering structure, and it disrupts key-based lookups. Clustered indexes suit tables queried primarily by that key order, not mixed filter-and-sort workloads.
- ✗
Create a nonclustered index on (OrderDate DESC) without including TotalAmount
Why it's wrong here
OrderDate alone cannot satisfy the CustomerID equality predicate, so the engine still scans or seeks on CustomerID separately, and TotalAmount must be fetched via key lookups rather than covered. It would suit pure date-range reporting, where CustomerID is absent and descending order matches the index.
Go deeper
Related to this question
Learn chapter
Azure Database for PostgreSQL
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.
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.
About these practice questions
This DP-900 question is part of Courseiva's 851-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.