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 CategoryID.

  Why it's wrong here

  A clustered index on CategoryID would physically reorder the entire table by Category, which could help group-related rows, but it would change the current clustered index from the primary key (ProductID), forcing primary key lookups to become key lookups into this new clustered index. More importantly, even with rows grouped by CategoryID, the query needs rows sorted by Price descending, and this index does not provide that ordering because within each CategoryID the Price values remain unsorted. The optimizer would still have to perform an explicit sort, so this does not satisfy the performance goal.

• ✗

  Create a nonclustered index on CategoryID that includes Price as an included column.

  Why it's wrong here

  This nonclustered index would cover the WHERE clause because both CategoryID and Price are present in the index; however, the composite key structure only sorts by CategoryID first. Since Price is an included column, its values are stored at the leaf level but are not part of the index key, so they are not ordered within each CategoryID group. Consequently, after the index seek on a specific CategoryID, SQL Server must collect all matching rows and then run a separate SORT operation to deliver the requested descending Price order, which is exactly the work the query is trying to avoid.

• ✓

  Create a nonclustered index on (CategoryID, Price) with Price in descending order.

  Why this is correct

  This index directly supports both the filter and the sort requirements because the index key columns are listed as (CategoryID, Price), and the Price column is explicitly designated as descending. For a given CategoryID, the index entries are stored in descending Price order, so the query optimizer can perform a seek to the first matching row and then scan forward to retrieve rows already sorted exactly as requested, eliminating the need for a sort operator. Additionally, if the query only references these two columns, the index is covering and avoids lookups, making it the optimal choice for this predicate and ordering.

• ✗

  Create a clustered columnstore index on the table.

  Why it's wrong here

  A clustered columnstore index stores table data in a columnar format that is heavily compressed and optimized for analytical workloads that scan large numbers of rows, such as aggregation and reporting queries. It is not designed for the point lookups typical of an OLTP application, and because columnstore segments do not maintain a row-level sort order on a specific column like Price, the engine cannot use the index to satisfy the ORDER BY without performing a full sort. Moreover, retrieving individual rows or small subsets from a columnstore requires reading whole row groups, which would be slow for this retail application's interactive queries, so this option would likely worsen performance.