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 OrderDate and a non-clustered index on OrderStatus

  Why it's wrong here

  Rebuilding the clustered index around OrderDate would physically reorder the entire table by that column, which can fragment data, create page splits, and slow inserts because OrderID's clustered key is lost as the logical row pointer. The separate non-clustered index on OrderStatus alone cannot efficiently serve a range filter on OrderDate, and because OrderDate is not included in that index, the query engine would need to perform key lookups and a sort. This design also fails to exploit the descending order needed for the ORDER BY clause, so it is not optimized for the requested workload.

• ✗

  Create a non-clustered index on (OrderDate, OrderStatus) and keep the existing clustered index on OrderID

  Why it's wrong here

  While a composite non-clustered index on (OrderDate, OrderStatus) does match the equality and range predicates, the default ascending sort order on OrderDate conflicts with the query's ORDER BY OrderDate DESC. The index can theoretically be scanned in reverse, but SQL Server may still introduce an explicit sort operator if the optimizer chooses a forward scan or if the index does not fully cover all selected columns. Additionally, because the index is non-clustered, any columns beyond OrderDate, OrderStatus, and the clustered key OrderID would require a lookup, preventing it from being a truly covering index for the query.

• ✗

  Create a clustered index on OrderID and a non-clustered index on (OrderStatus, OrderDate)

  Why it's wrong here

  This option is problematic because the non-clustered index is ordered by OrderStatus first and then OrderDate, which means the leading column OrderStatus does not help narrow the results for a predicate on OrderDate alone. The clustered index on OrderID is unchanged, so it does not support the query's sorting or filtering requirements; the query engine would have to scan a large portion of the index and perform a separate sort operation. Furthermore, the absence of DESC on OrderDate means the index cannot return rows in the required order without additional processing, making this a poor choice for the specific query.

• ✓

  Create a non-clustered index on (OrderDate DESC, OrderStatus) and keep the existing clustered index on OrderID

  Why this is correct

  This is correct because the non-clustered index uses OrderDate as the leading column with DESC, which exactly matches the ORDER BY OrderDate DESC requirement and allows the query engine to read rows in the correct order without a sort. Including OrderStatus as a second key column lets the index efficiently handle filtering on both columns, and because OrderID is the clustered key it is automatically appended to non-clustered index entries, making the index covering for a query selecting OrderID, OrderDate, and OrderStatus. Keeping the existing clustered index on OrderID preserves the primary key's uniqueness and avoids unnecessary physical table reorganization, so this design balances performance for the query with minimal impact on other operations.

Go deeper

Related to this question