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 nonclustered index on CustomerID including OrderDate.

  Why it's wrong here

  This index would support an equality seek on CustomerID, locating rows for a given customer quickly. However, because OrderDate is stored only as an included column, it is not part of the index key, so the Database Engine cannot use the index to return rows already sorted by OrderDate. After retrieving the matching rows, a separate sort operator will be added to the query plan, which adds CPU and memory overhead. For optimal speed, the sort column must be a key column in the index.

• ✓

  Create a nonclustered index on (CustomerID, OrderDate DESC) with included columns for other needed columns.

  Why this is correct

  This is the correct design because it creates a covering, composite index whose leftmost key column matches the equality filter on CustomerID, while the second key column OrderDate is flagged DESC to match the ORDER BY direction. The query optimizer can perform an index seek on CustomerID and then read rows from the index in exactly the required sort order, eliminating the sort operator entirely. By including any additional columns referenced in the SELECT list, the index becomes covering, avoiding expensive lookups back to the clustered index.

• ✗

  Rebuild the clustered index to be on CustomerID.

  Why it's wrong here

  Rebuilding the clustered index on CustomerID would change the physical ordering of the entire table and force all existing nonclustered indexes to update their row locators, causing substantial fragmentation and I/O overhead. More importantly, a clustered index key does not eliminate the need for a sort unless its key includes OrderDate as a secondary column; CustomerID alone only groups rows, it does not sort them by order date. Since CustomerID is not unique, it also violates the common requirement that clustered indexes be unique or narrow, and this would disrupt the primary key on OrderID.

• ✗

  Create a nonclustered index on OrderDate.

  Why it's wrong here

  An index on OrderDate alone is appropriate for queries that order or filter solely by order date, but it cannot efficiently handle the combination of an equality predicate on CustomerID and a sort on OrderDate. Because the index key starts with OrderDate, the Database Engine cannot seek directly on CustomerID; it would need to scan the index and apply a residual predicate, or scan then sort, leading to a high number of logical reads. Composite indexes must list equality columns before ordering columns to support both seek and ordered output.