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 CustomerId

  Why it's wrong here

  A clustered index determines the physical storage order of all rows in the table, so reassigning it to CustomerId would rewrite the entire table and potentially disrupt performance for queries that rely on the existing clustered key (typically the primary key). For the slow query filtering on CustomerId and OrderDate, this change does not help because the clustered index alone does not include OrderDate as a key column; matching orders for a customer would still require scanning all their rows and filtering on OrderDate. Moreover, the current clustered key may already serve other lookups, and altering it would incur significant overhead and lock time.

• ✗

  Partition the table by OrderId

  Why it's wrong here

  Table partitioning physically splits data into separate storage segments based on the partition key, and it improves performance mainly when queries and maintenance operations align with that key. Partitioning by OrderId would not help a query that filters on CustomerId and OrderDate, because the query's predicates do not include OrderId, so it cannot use partition elimination and must still scan all partitions. In fact, partitioning by an irrelevant key can add overhead, as each partition may require separate index maintenance and B-tree traversals, making the query slower rather than faster.

• ✓

  Create a nonclustered index on (CustomerId, OrderDate)

  Why this is correct

  A nonclustered index on (CustomerId, OrderDate) is the optimal choice because it lets the query optimizer seek directly to the index entry for a specific CustomerId and then perform a range scan on OrderDate within that customer's contiguous block of index rows. Since both columns are key columns, the index can also act as a covering index for the query if only these columns are needed, avoiding costly lookups to the clustered index. This arrangement is ideal for equality on the leading column and inequality/range on the second, which is exactly the pattern of the slow query.

• ✗

  Create a nonclustered index on (OrderDate, CustomerId)

  Why it's wrong here

  When OrderDate leads the composite nonclustered index, the rows are sorted first by date and then by CustomerId, so the index is not aligned with the query's primary filtering column. To find a specific customer's orders, the optimizer must scan a range of dates and then test each row's CustomerId, instead of using a direct seek; this is especially inefficient if the query also filters by OrderDate with a range, because the second column cannot be used for seeking once a range predicate is applied to the first. A query with a constant CustomerId and a date range would require a full or partial scan of the index, making it slower than the correctly ordered index.

Go deeper

Related to this question