DP-900 Practice Question: Identify considerations for relational data on Azure
An e-commerce application uses Azure SQL Database. The Orders table stores millions of rows with columns: OrderID (primary key, clustered index), CustomerID, OrderDate, OrderStatus, TotalAmount. Queries frequently filter on OrderDate and OrderStatus, and sort results by OrderDate DESC. Which indexing strategy will most improve query performance for these filters and sort?
⚠ Common exam trap
Candidates often think a single-column index on the most filtered column (OrderDate) is sufficient, overlooking that the second filter (OrderStatus) and the sort order require a composite index to avoid extra processing.
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, OrderStatus and include other columns needed by the query.
A nonclustered index on (OrderDate, OrderStatus) supports both the filter and the sort in a single index seek/scan. SQL Server can use the index to locate rows matching both predicates and return them already sorted by OrderDate DESC without a separate sort operation, which is critical for performance on 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 only.
Why it's wrong here
An index on OrderDate alone would help with filtering by OrderDate and sorting, but it would not help with the additional filter on OrderStatus. The database would still need to filter by OrderStatus after the seek, which may be less efficient.
- ✓
Create a nonclustered index on OrderDate, OrderStatus and include other columns needed by the query.
Why this is correct
A composite nonclustered index on (OrderDate, OrderStatus) allows the query to filter on both columns efficiently and provides the data already sorted by OrderDate (the leading key). Including other columns avoids key lookups, making the query even faster.
- ✗
Create a clustered columnstore index on the table.
Why it's wrong here
Clustered columnstore indexes are designed for large analytical workloads that scan many rows, not for OLTP queries that filter on specific columns and sort. They may degrade performance for point queries and range scans with sort operations.
- ✗
Create a nonclustered index on OrderStatus only.
Why it's wrong here
An index on OrderStatus alone helps with filtering on OrderStatus, but does not help with the OrderDate filter or sorting by OrderDate. The database would still need to sort the results or scan many rows.
Go deeper
Related to this question
Learn chapter
Azure SQL Services
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.
Key term
Primary key
A primary key is a unique identifier for each record in a database table, ensuring that no two rows have the same value in that column.
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 →
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.