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: Create a nonclustered index on (DepartmentID, LastName) INCLUDE (Salary)

  Why this is correct

  A nonclustered index on (DepartmentID, LastName) INCLUDE (Salary) is a covering index specifically designed for this query: it contains every column needed in the WHERE, SELECT, and ORDER BY clauses within the index leaf level. Because the index is sorted by DepartmentID first and LastName second, the SQL Server query optimizer can perform an index seek for the specific department, then navigate to the exact last names, and retrieve Salary directly from the included column. This completely avoids the need for expensive key lookups into the clustered index, making it the most efficient access path for this workload.

• ✗

  B: Create a nonclustered index on LastName only

  Why it's wrong here

  An index on LastName alone would only help if the query filtered primarily by LastName without a leading DepartmentID predicate. With a filter on DepartmentID and LastName, the optimizer could still seek on LastName only after scanning or using a residual predicate for the department, or it might choose to scan the entire nonclustered index and then perform key lookups for each matching row to retrieve Salary. Because Salary is not included, every row that matches the nonclustered index condition would require a lookup into the clustered index, causing excessive I/O and defeating the purpose of the index.

• ✗

  C: Create a nonclustered index on DepartmentID and another nonclustered index on LastName

  Why it's wrong here

  Creating two separate nonclustered indexes—one on DepartmentID and one on LastName—does not give the same benefit as a single composite index. The optimizer might attempt an index intersection of both indexes to narrow the set of rows, but that process requires probing both indexes and then still needs to look up Salary from the clustered index for every surviving row. The overhead of index intersection plus key lookups is typically far higher than a single covering composite index, especially for a selective filter like a specific department and last name.

• ✗

  D: Change the clustered index to (DepartmentID, LastName)

  Why it's wrong here

  Changing the clustered index to (DepartmentID, LastName) would physically reorder the entire table by that key, which directly contradicts the existing clustered index that likely enforces the primary key on EmployeeID or similar. This would disrupt the ordering of the base table, breaking the performance of any query that relies on EmployeeID ordering or seeking by employee, and it can cause severe logical fragmentation and page splits during concurrent HR transactions. Moreover, even with a clustered key on DepartmentID, LastName, the Salary column is not part of the index key or included columns, so the query would still need to access the row's data page—no covering benefit—and the physical redesign is invasive and irreversible without significant downtime.

Go deeper

Related to this question