DP-900 Practice Question: Identify considerations for relational data on Azure
A company uses Azure SQL Database to store order data. The Orders table has millions of rows with columns: OrderID (primary key, clustered), CustomerID, OrderDate, Status, TotalAmount. Queries frequently filter on OrderDate and Status, and sort results by OrderDate descending. Which indexing strategy will most improve query performance for these filters and sort?
⚠ Common exam trap
Watch out — candidates often think a clustered index on the filter column is always best, but they overlook that the existing clustered index on OrderID is needed for primary key enforcement and that a covering nonclustered index is the optimal way to support specific query patterns without disrupting the table's physical design.
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) and include TotalAmount
Creates a covering nonclustered index on (OrderDate DESC, Status) that directly supports the frequent filter on OrderDate and Status and the ORDER BY OrderDate DESC. Including TotalAmount as a non-key column makes the index covering, so all needed columns come from the index without key lookups, maximizing query performance.
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 OrderDate
Why it's wrong here
Changing the clustered index to OrderDate would improve range scans on OrderDate but the clustered index physically orders the data; however, the primary key OrderID would then be a nonclustered index, which may affect other queries. A composite nonclustered index is more targeted.
- ✓
Create a nonclustered index on (OrderDate DESC, Status) and include TotalAmount
Why this is correct
This composite index covers both filter columns in the correct sort order and includes the TotalAmount column, making the query fully covered without needing to access the table. This yields the best performance for the described queries.
- ✗
Create a nonclustered index on Status alone
Why it's wrong here
A single-column index on Status helps filter by Status, but the query also filters on OrderDate and sorts by OrderDate. Without an index on OrderDate, the database must perform a sort after filtering, which is slower.
- ✗
Create a clustered columnstore index on the table
Why it's wrong here
Columnstore indexes are designed for analytical queries that aggregate large volumes of data, not for point queries or range scans with sort on a specific date range. They are not optimal for this OLTP-style query.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Column
A column is a vertical set of values in a database table that stores one specific type of attribute for every row.
Key term
Table
A table is a structured collection of data organized into rows and columns, used in databases and spreadsheets to store and manage information efficiently.
About these practice questions
Courseiva writes every DP-900 question from scratch — 820 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
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.