DP-900 Practice Question: Identify considerations for relational data on Azure

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 OrderDate only.

  Why it's wrong here

  An index on OrderDate alone would help date range scans, but still requires filtering on CustomerID, leading to key lookups or residual predicates. It is not as efficient as a composite index that includes CustomerID.

• ✓

  A nonclustered index on (CustomerID, OrderDate DESC) including TotalAmount as included column.

  Why this is correct

  This composite index is optimal because the leading key column CustomerID enables a precise equality seek to exactly the rows for the specified customer. The OrderDate DESC key column then provides both an efficient range scan for the date condition and returns rows already in the required ORDER BY OrderDate DESC order, eliminating a sort operator. Adding TotalAmount as an included column makes the index fully covering: all columns referenced in the SELECT, WHERE, and ORDER BY are present in the index, so SQL Server can satisfy the query entirely from the nonclustered index without costly key lookups into the clustered index or heap.

• ✗

  A clustered index on (OrderDate, CustomerID).

  Why it's wrong here

  A clustered index on (OrderDate, CustomerID) forces a scan of all rows for the matching CustomerID because the leading key is OrderDate, not the equality filter column. It is tempting because ordering by OrderDate DESC is already satisfied by the index order, but without CustomerID leading, the query cannot seek directly to the customer’s rows. This index would be correct if the query filtered primarily on a date range without an equality predicate on CustomerID.

• ✗

  A nonclustered index on (OrderDate DESC) only.

  Why it's wrong here

  An index on OrderDate DESC alone cannot support a seek on CustomerID because CustomerID is not the leading key column. When the query filters on CustomerID with equality, SQL Server would have to either scan the entire nonclustered index (or clustered index) and check each row's CustomerID, or perform key lookups for every matching OrderDate range row, which is extremely inefficient for a large orders table. While the index does preserve OrderDate DESC order, that benefit is largely irrelevant because the CustomerID filter prevents a direct seek to the relevant rows, and the missing TotalAmount still forces additional key lookups to retrieve that column. Effectively, this index is no more useful for this query than a clustered index scan with a sort, making it a poor choice compared to the composite covering index.

Go deeper

Related to this question