DP-900 Practice Question: Identify considerations for relational data on Azure
A company uses Azure SQL Database for an e-commerce application. The Orders table has millions of rows. Queries that filter on CustomerID and order by OrderDate are slow. The table currently has a clustered index on OrderID (the primary key). Which index strategy will best improve these queries?
⚠ Common exam trap
Watch out — candidates often think a single-column index on the filter column (CustomerID) or the sort column (OrderDate) is sufficient, but they miss that a composite index covering both in the correct order eliminates the need for a separate sort and key lookups, which is critical for large tables in Azure SQL Database.
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
✓
C. Create a nonclustered index on (CustomerID, OrderDate).
Creating a nonclustered index on (CustomerID, OrderDate) directly supports the query's filter (WHERE CustomerID = ?) and sort (ORDER BY OrderDate) operations. This composite index allows SQL Server to seek on CustomerID and then retrieve rows in OrderDate order without a separate sort, eliminating the need for a full clustered index scan on OrderID. It is a covering index for this query pattern, significantly reducing I/O and CPU overhead.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
A. Create a nonclustered index on OrderDate only.
Why it's wrong here
An index on OrderDate alone places OrderDate as the leading key, so it can efficiently support ordering by OrderDate but offers no help for an equality predicate on CustomerID. Without CustomerID as a leading key, the query engine cannot seek to a specific customer's rows; it would instead scan the entire index or a large portion and then apply a separate sort or hash match to satisfy the ORDER BY. In most cases, this index would be ignored for the query because it neither narrows the scan on CustomerID nor eliminates the need to sort all matching rows.
- ✗
B. Create a filtered index on CustomerID where Status = 'Active'.
Why it's wrong here
A filtered index on CustomerID WHERE Status = 'Active' only persists rows that meet the filter condition, so any order with a Status other than 'Active' is absent from the index. If the reporting query filters only by CustomerID (with no Status predicate), rows for that customer with inactive orders would be missing from the index, forcing the optimizer to combine a partial index scan with lookups to the base table or abandon the index entirely. Additionally, the index's key is only CustomerID, so it does not include OrderDate and therefore cannot support the ORDER BY clause. The index only helps if every query on this table reliably includes a Status = 'Active' predicate, which is not the case here.
- ✓
C. Create a nonclustered index on (CustomerID, OrderDate).
Why this is correct
This composite nonclustered index places CustomerID as the leading key, enabling the query optimizer to perform an efficient index seek on the equality predicate CustomerID = @Customer. Because OrderDate is the second column, all rows for a given customer are stored in ascending OrderDate order, so the storage engine can return the results in the required sorted order without a separate SORT operator. If the SELECT list is limited to CustomerID, OrderDate, and perhaps other included columns, the index can also be covering, further eliminating base-table lookups. This design directly matches the WHERE and ORDER BY patterns and is the recommended approach for this query.
- ✗
D. Create a nonclustered index on OrderID and OrderDate.
Why it's wrong here
This index uses OrderID as the leading key and OrderDate as the second key, but it lacks CustomerID entirely, so it cannot be used to seek on the CustomerID predicate. Because OrderID is likely the primary key or a unique identifier, each index row corresponds to a different order, meaning the index provides no clustering of rows for a given customer. To answer the query, the optimizer would have to scan substantially all index entries and perform key lookups to retrieve rows for the desired customer, and it would still need to sort the results because OrderDate is not ordered within a customer's set. Thus, this index is essentially useless for this workload and would only add write overhead.
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
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.