Courseiva
← DP-900
Full test
Identify considerations for relational data on Azure →mediumMultiple ChoiceObjective-mapped

Covering Index with Sort Order for Azure SQL Database

A company uses Azure SQL Database for an order management system. The Orders table has millions of rows with columns: OrderID (primary key, clustered), CustomerID, OrderDate, Status (e.g., 'Shipped', 'Pending'), TotalAmount. Queries frequently filter on OrderDate and Status, and sort results by OrderDate in descending order. They return several columns including TotalAmount. Which indexing strategy will most improve query performance?

Quick Answer

The correct answer is to create a nonclustered index on (OrderDate DESC, Status) INCLUDE (CustomerID, TotalAmount). This strategy works because it builds a covering index with sort order for Azure SQL Database, meaning the index itself contains all columns needed by the query—both the filtered columns and the returned columns—so SQL Server can resolve the query entirely from the index without touching the clustered index. On the DP-900 exam, this tests your understanding of how covering indexes eliminate costly key lookups and how specifying a descending sort order in the index definition avoids an extra sort operation for ORDER BY DESC clauses. A common trap is to create the index without the INCLUDE clause or to forget the descending order, which forces either a lookup or an explicit sort. Memory tip: “Cover and sort in one pass”—if your index covers the query and matches the sort direction, you skip both the lookup and the sort.

⚠ Common exam trap

It's easy for candidates to think a clustered index on the filter columns is always best, but they forget that the clustered index already exists on OrderID and that a covering nonclustered index with included columns is more efficient for specific query patterns without disrupting the existing table structure.

Answer choices

Why each option matters

Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.

Correct answer & explanation

✓

Create a nonclustered index on (OrderDate DESC, Status) INCLUDE (CustomerID, TotalAmount)

It creates a covering nonclustered index that matches the query's filter and sort order exactly. The index on (OrderDate DESC, Status) allows SQL Server to seek directly on OrderDate and Status, and the descending order avoids a sort operation for the ORDER BY OrderDate DESC clause. Including CustomerID and TotalAmount as included columns makes this a covering index, so the query can be satisfied entirely from the index without key lookups to the clustered index, which is critical for performance on a table with millions of rows.

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 DESC, Status) INCLUDE (CustomerID, TotalAmount)

    Why this is correct

    Correct. This index matches the filter columns in the correct order and includes the ORDER BY direction. Included columns cover additional columns needed, making it a covering index for many queries.

  • ✗

    Create a clustered index on (OrderDate, Status)

    Why it's wrong here

    Incorrect. The primary key already creates a clustered index on OrderID. Changing it would require redesigning the table. Also, a clustered index on (OrderDate, Status) may not be selective enough and does not help ORDER BY DESC.

  • ✗

    Create a nonclustered index on (Status) only

    Why it's wrong here

    Incorrect. This only helps the filter on Status, but does not support the OrderDate filter or the sort order. Additional processing would be needed.

  • ✗

    Create a columnstore index on the entire table

    Why it's wrong here

    Incorrect. Columnstore indexes are best for large aggregation queries on many columns, not for point-lookup or range queries with sort order. This would likely hurt performance for these OLTP-style queries.

About these practice questions

This DP-900 question is part of Courseiva's 820-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →

How Courseiva writes practice questions · Editorial policy

Same concept, more angles

1 more way this is tested on DP-900

These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.

Variation 1. A retail company uses Azure SQL Database for an order management system. The Orders table has columns: OrderID (primary key, clustered), CustomerID, OrderDate, TotalAmount. Queries frequently filter on CustomerID and OrderDate, and sort results by OrderDate in descending order. The queries also return the TotalAmount. Which indexing strategy will most improve query performance for these operations?

medium
  • A.Maintain the existing clustered index on OrderID only.
  • ✓ B.Create a nonclustered index on (CustomerID, OrderDate DESC) INCLUDE (TotalAmount).
  • C.Create a nonclustered index on (OrderDate DESC) INCLUDE (CustomerID, TotalAmount).
  • D.Create a clustered columnstore index on the entire table.

Why B: It creates a covering nonclustered index that supports both the filter predicates (CustomerID and OrderDate) and the sort order (OrderDate DESC) while including TotalAmount as an included column to avoid key lookups. This index allows SQL Server to satisfy the query entirely from the index pages, minimizing I/O and improving performance.

JA

Written by Johnson Ajibi, MSc IT Security

Senior Network & Security Engineer · founder of Courseiva

This DP-900 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-900 exam.