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.

• ✗

  Create a clustered index on OrderID.

  Why it's wrong here

  A clustered index on OrderID defines the physical sort order of the table, but the queries in question filter by CustomerID and OrderDate, not by OrderID. Without include columns or a supporting nonclustered index, SQL Server would have to perform a full clustered index scan, reading every order row to find matching customers and dates. Although a clustered index is essential for key lookups and primary key enforcement, choosing OrderID as the clustering key does nothing to accelerate the specific predicates, and it may even consume I/O and storage needlessly for this workload.

  When this WOULD be correct

  If the question asked for the best index to improve performance of queries that retrieve a single order by its OrderID, then a clustered index on OrderID would be correct.

• ✓

  Create a nonclustered index on (CustomerID, OrderDate).

  Why this is correct

  This composite nonclustered index is precisely tuned for queries that filter by a specific CustomerID and a range of OrderDate values. The leftmost key column CustomerID enables an equality seek, immediately narrowing the scan to that customer's rows, while OrderDate as the second key column lets SQL Server traverse a contiguous, ordered subset of the B-tree for the date range instead of scanning the entire table. Because the index stores both filter keys together, it also reduces the number of key lookups needed to retrieve the row data, making it the most efficient choice for the described e-commerce workload.

• ✗

  Create a nonclustered index on (OrderDate, CustomerID).

  Why it's wrong here

  Although both columns are included, placing OrderDate first is less efficient because the index will first sort by date, then customer. This can lead to more index scanning when filtering by a specific customer over a date range.

  When this WOULD be correct

  If queries frequently filter by OrderDate alone (e.g., all orders on a specific date) and only occasionally by CustomerID, then a nonclustered index on (OrderDate, CustomerID) would be correct because it supports the primary filter (OrderDate) efficiently.

• ✗

  Create a nonclustered index on TotalAmount.

  Why it's wrong here

  An index on TotalAmount alone contains no CustomerID or OrderDate columns, so it cannot support the predicates in the target queries. SQL Server would be unable to seek on CustomerID, and the index is not ordered by date, so it provides no benefit for date-range filtering. This index would only improve performance for queries that filter, sort, or aggregate by TotalAmount, which is not the workload described, meaning it would likely be ignored or require a full scan if used.

  When this WOULD be correct

  If the query frequently filters or aggregates by TotalAmount (e.g., 'SELECT SUM(TotalAmount) FROM Orders WHERE TotalAmount > 100'), a nonclustered index on TotalAmount would improve performance by allowing index seeks or covering scans.

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.

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?”