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 (Author, PublishedYear DESC) and include (Title, CopiesAvailable)

  Why this is correct

  A composite nonclustered index with Author as the leading key column lets Azure SQL Database seek directly to the rows for the specified author. Adding PublishedYear DESC as the second key column means rows are already stored in the required sort order, eliminating a sort operator. Including Title and CopiesAvailable makes the index covering, so the query engine returns the result using only index pages, avoiding costly bookmark lookups into the clustered index. This design addresses the filter, ordering, and projection in one pass.

• ✗

  Create a nonclustered index on Author only

  Why it's wrong here

  An index on Author alone improves row location but does nothing to satisfy the ORDER BY PublishedYear DESC. After finding all matching rows, SQL Server must fetch the full rows from the base table and then sort them, which requires memory and can spill to tempdb if the result set is large. Because Title and CopiesAvailable are not included in the index, every hit also requires a bookmark lookup to the clustered index. The index is therefore only a partial solution and can still perform poorly for prolific authors.

• ✗

  Create a nonclustered index on PublishedYear DESC

  Why it's wrong here

  An index on PublishedYear DESC is aligned with the sort order but has the wrong leading key for the query's equality predicate on Author. With Author as the filter, this index cannot provide a seek; SQL Server would have to scan index entries across many years, checking the Author value on each row. This scan may actually be more expensive than the original clustered index scan because it covers only one column and then still needs lookups to retrieve Title and CopiesAvailable. Without Author as the leading key, the index is ineffective for this specific workload.

• ✗

  Keep only the existing clustered index on BookID

  Why it's wrong here

  Relying only on the clustered index on BookID means the database has no index with Author as a key column, so the query engine is forced to do a full scan of every row in the table to find matching books. As the library's books table grows, this scan reads far more pages than necessary and consumes more I/O, CPU, and DTU/DTU burst resources in Azure SQL Database. The clustered index is excellent for point lookups by BookID but irrelevant to an Author-based search. A full scan is the worst-case execution plan for this query pattern.

Go deeper

Related to this question