DP-900 Practice Question: Identify considerations for relational data on Azure
A company has an Azure SQL Database with an 'Orders' table containing millions of rows. The table has a clustered index on OrderID (primary key). Queries frequently filter by CustomerID (equality) and OrderDate (range). These queries are slow and cause high logical reads. Which index strategy will most improve performance for these specific queries?
⚠ Common exam trap
A common mix-up: candidates think a filtered index or a single-column index is sufficient, but they overlook that the query has both an equality and a range predicate, requiring a composite index that supports both in the correct order (equality first, range second) to achieve optimal seek + range scan performance.
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 non-clustered index on (CustomerID, OrderDate).
A non-clustered index on (CustomerID, OrderDate) is a covering index for queries filtering by CustomerID (equality) and OrderDate (range). It allows SQL Server to perform an index seek on CustomerID, then a range scan on OrderDate, retrieving all needed columns without touching the clustered index (if the query is covered). This dramatically reduces logical reads compared to a full clustered index scan or a key lookup.
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 non-clustered index on (CustomerID, OrderDate).
Why this is correct
A non-clustered index with leading key CustomerID and second key OrderDate directly supports the query's equality filter on CustomerID and range filter on OrderDate. The optimizer can perform an index seek on CustomerID and then a range seek on OrderDate within that customer's rows, avoiding a full table scan and minimizing page reads. If the SELECTed columns are included or the query is otherwise covered, this index can be particularly efficient, but even as a key-only index it reduces lookups compared to single-column alternatives.
- ✗
Rebuild the clustered index on (OrderDate, CustomerID).
Why it's wrong here
Rebuilding the clustered index to use (OrderDate, CustomerID) would physically reorder the entire table, causing heavy fragmentation and page splits, and would potentially degrade workloads that depend on the current OrderID-based clustering. More fundamentally, OrderDate as the leading key is incompatible with this query's equality predicate on CustomerID: the engine would still need to range-access all customers' orders or rely on a nested loop over CustomerID values. Such a change is a major schema modification with broad performance consequences, far beyond the local benefit of optimizing a single index, making it a poor first choice.
When this WOULD be correct
If the queries frequently filter by OrderDate (range) and CustomerID (equality) AND the table is primarily accessed via these columns (e.g., no frequent lookups by OrderID), then making (OrderDate, CustomerID) the clustered index could be correct. This would be in a scenario where OrderID is not the primary key or is rarely used for lookups.
- ✗
Create a non-clustered index on OrderDate.
Why it's wrong here
An index on OrderDate alone uses OrderDate as the leading key, so the database cannot seek directly on CustomerID when that column is the primary equality predicate. To answer a query filtering by both CustomerID and a date range, the engine would either scan the OrderDate index and then filter CustomerID, or seek on OrderDate and perform a large number of key lookups to retrieve the CustomerID for each row. Because the query likely needs rows for a single customer across many dates, the OrderDate index does not reduce the working set enough and performs far worse than a composite index with CustomerID first.
When this WOULD be correct
If queries filter only by OrderDate (e.g., 'WHERE OrderDate BETWEEN ...') without any CustomerID condition, a non-clustered index on OrderDate would be optimal for range scans.
- ✗
Create a filtered index on OrderDate for recent dates.
Why it's wrong here
A filtered index with a predicate like WHERE OrderDate >= '2024-01-01' is only useful when the query's predicate matches or is more restrictive than that filter; a general query with a historical date range or with no date cutoff would not match the index's filter. Additionally, this index has OrderDate as key but no CustomerID as a leading column, so it cannot efficiently satisfy the CustomerID equality predicate. It would be ignored or underutilized for most variations of the order lookup and adds overhead for writes, making it overly specialized and ineffective for the stated workload.
When this WOULD be correct
If the question stated that queries always filter on OrderDate for recent dates (e.g., last 30 days) and never filter by CustomerID, then a filtered index on OrderDate for recent dates would be optimal, as it reduces index size and maintenance overhead while covering the range predicate.
Option-by-option analysis
Why each answer is right or wrong
Understanding why wrong answers are wrong — and when they would be correct — is what separates a 750 score from a 900. The DP-900 exam frequently reuses these exact scenarios with slightly different constraints.
✓Create a non-clustered index on (CustomerID, OrderDate).Correct answer▾
Why this is correct
A non-clustered index with leading key CustomerID and second key OrderDate directly supports the query's equality filter on CustomerID and range filter on OrderDate. The optimizer can perform an index seek on CustomerID and then a range seek on OrderDate within that customer's rows, avoiding a full table scan and minimizing page reads. If the SELECTed columns are included or the query is otherwise covered, this index can be particularly efficient, but even as a key-only index it reduces lookups compared to single-column alternatives.
✗Rebuild the clustered index on (OrderDate, CustomerID).Wrong answer — click to see why▾
Why this is wrong here
Rebuilding the clustered index on (OrderDate, CustomerID) would change the physical order of the table, which is inefficient because the primary key (OrderID) is typically used for unique row identification and joins. The queries filter by CustomerID (equality) and OrderDate (range), so a non-clustered index covering both columns is more appropriate without disrupting the clustered index structure.
★ When this WOULD be the correct answer
If the queries frequently filter by OrderDate (range) and CustomerID (equality) AND the table is primarily accessed via these columns (e.g., no frequent lookups by OrderID), then making (OrderDate, CustomerID) the clustered index could be correct. This would be in a scenario where OrderID is not the primary key or is rarely used for lookups.
Why candidates choose this
Candidates may think that changing the clustered index to match the query filter columns will directly improve performance, not realizing that the clustered index defines the physical order and should align with the most critical access pattern, which here is likely the primary key.
✗Create a non-clustered index on OrderDate.Wrong answer — click to see why▾
Why this is wrong here
An index on OrderDate alone does not cover the CustomerID filter, so SQL Server may still need to perform key lookups or scan a large portion of the index, failing to efficiently support both equality and range predicates.
★ When this WOULD be the correct answer
If queries filter only by OrderDate (e.g., 'WHERE OrderDate BETWEEN ...') without any CustomerID condition, a non-clustered index on OrderDate would be optimal for range scans.
Why candidates choose this
Candidates recognize that OrderDate is used in range queries and assume a single-column index is sufficient, overlooking the need to also cover the equality filter on CustomerID for maximum performance.
✗Create a filtered index on OrderDate for recent dates.Wrong answer — click to see why▾
Why this is wrong here
A filtered index on OrderDate for recent dates would only improve queries that filter on recent dates, but the question specifies queries filter by CustomerID (equality) and OrderDate (range). The filtered index does not include CustomerID, so it cannot efficiently support the equality filter on CustomerID, leading to key lookups or scans.
★ When this WOULD be the correct answer
If the question stated that queries always filter on OrderDate for recent dates (e.g., last 30 days) and never filter by CustomerID, then a filtered index on OrderDate for recent dates would be optimal, as it reduces index size and maintenance overhead while covering the range predicate.
Why candidates choose this
Candidates may think a filtered index is always better because it is smaller and more efficient, but they overlook that the index must include all columns used in the query's WHERE clause (CustomerID) to be truly covering or highly selective.
Analysis generated from the official DP-900blueprint and verified against question context. The “when correct” sections are what AI assistants cite when candidates ask “what’s the difference between these options?”
Go deeper
Related to this question
Learn chapter
Azure SQL Services
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.
Key term
Index
An index is a data structure that speeds up data retrieval operations on a database table or file, much like a book index helps you find topics quickly.
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.