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 (OrderDate, CustomerID)

  Why this is correct

  A composite nonclustered index on (OrderDate, CustomerID) is ideal for a query filtering on OrderDate and ordering by CustomerID. SQL Server can perform an index seek on the leading OrderDate column to isolate the matching rows, and because CustomerID is the second key column, the rows are already returned in the required sort order—eliminating the need for a separate SORT operator and reducing query cost significantly.

• ✗

  Create a nonclustered index on (CustomerID, OrderDate)

  Why it's wrong here

  This index is incorrect because the leading key column, CustomerID, does not match the equality/range predicate on OrderDate. Due to the leftmost prefix rule of composite indexes, SQL Server cannot seek on OrderDate unless it appears first; instead, it would have to scan the entire nonclustered index and then sort the results by CustomerID, or perform a key lookup and sort—both of which are far less efficient than using an index with OrderDate as the leading column.

• ✗

  Change the clustered index to (OrderDate)

  Why it's wrong here

  Changing the clustered index to (OrderDate) would make OrderDate the physical ordering key of the entire table, forcing SQL Server to rewrite all rows and adjust the clustered index key for every operation. This also removes the original key (such as OrderID) from the clustered index, which can degrade performance for point lookups, inserts, and page splits, and it disrupts any other indexes that depend on the existing clustered key—so a separate nonclustered index is the appropriate, less invasive solution.

• ✗

  Create a filtered index on OrderDate WHERE CustomerID IS NOT NULL

  Why it's wrong here

  A filtered index defined as ON OrderDate WHERE CustomerID IS NOT NULL still has only OrderDate as its key column, so it does not provide any ordering by CustomerID; the query engine would still need to perform a sort after reading matching rows. Moreover, the filter predicate (CustomerID IS NOT NULL) does not typically match the query's WHERE clause, meaning the optimizer might not even consider this index for the specific query, making it an ineffective and overly narrow solution.