The answer is a non-clustered index on (CustomerID, OrderDate) INCLUDE (TotalAmount). This is correct because it creates a covering index with INCLUDE columns in Azure SQL Database, allowing the query to filter on both CustomerID and OrderDate via an index seek while keeping TotalAmount in the leaf pages as a non-key column—eliminating expensive key lookups to the clustered index. On the DP-900 exam, this tests your understanding of how covering indexes reduce I/O by satisfying the entire query from the index alone; a common trap is choosing a clustered index or a single-column index, which would still require lookups for TotalAmount. Remember the mnemonic "SEEK + COVER" — the index seek handles the filter, and the INCLUDE columns cover the output, so no extra trips to the table are needed.
DP-900 Practice Question: Identify considerations for relational data on Azure
This DP-900 practice question tests your understanding of identify considerations for relational data on azure. Match the stated requirement to the specific cloud service, access model, or configuration option — many options are valid in isolation but not for this scenario. A key principle to apply: non-clustered indexes store data separately from the table, containing pointers to the actual data rows.. Once you have made your selection, read the full explanation to reinforce the concept and understand why each distractor is designed to mislead on exam day.
A company uses Azure SQL Database for an order management system. The Orders table has columns: OrderID (int, primary key), CustomerID (int), OrderDate (datetime), Status (varchar), TotalAmount (decimal). Queries frequently filter on CustomerID and OrderDate to find orders from a specific customer within a date range. Which index would most improve performance for these queries?
Clue words in this question
Noticing these words before you look at the options changes how you read each choice.
Clue: "primary"
Why it matters: Asks for the main purpose or function, not a secondary benefit. Eliminate answers that describe side-effects or partial functions.
Why wrong: This index optimizes lookups by OrderID but does not help queries filtering on CustomerID and OrderDate.
B
A non-clustered index on Status
Why wrong: Filtering on Status is not the frequent query pattern; this index does not support the CustomerID and OrderDate filters.
C
A non-clustered index on (CustomerID, OrderDate) INCLUDE (TotalAmount)
This composite index directly supports the filter predicate (CustomerID and OrderDate) and includes TotalAmount to make it a covering index, reducing I/O.
D
A non-clustered index on (OrderDate, TotalAmount)
Why wrong: Although OrderDate is used, the leading column is not CustomerID, so the index is less selective and may not be as effective for this query.
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
A non-clustered index on (CustomerID, OrderDate) INCLUDE (TotalAmount)
The query filters on CustomerID and OrderDate, so a composite non-clustered index on (CustomerID, OrderDate) allows SQL Server to perform an index seek on both columns, drastically reducing the number of rows scanned. Including TotalAmount as a non-key column makes this a covering index, meaning all needed data (including TotalAmount) is in the index leaf pages, avoiding costly key lookups to the clustered index.
Key principle: Non-clustered indexes store data separately from the table, containing pointers to the actual data 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.
✗
A clustered index on OrderID
Why it's wrong here
This index optimizes lookups by OrderID but does not help queries filtering on CustomerID and OrderDate.
✗
A non-clustered index on Status
Why it's wrong here
Filtering on Status is not the frequent query pattern; this index does not support the CustomerID and OrderDate filters.
✓
A non-clustered index on (CustomerID, OrderDate) INCLUDE (TotalAmount)
Why this is correct
This composite index directly supports the filter predicate (CustomerID and OrderDate) and includes TotalAmount to make it a covering index, reducing I/O.
Clue confirmation
The clue word "primary" in the question point toward this answer.
Related concept
Non-clustered indexes store data separately from the table, containing pointers to the actual data rows.
✗
A non-clustered index on (OrderDate, TotalAmount)
Why it's wrong here
Although OrderDate is used, the leading column is not CustomerID, so the index is less selective and may not be as effective for this query.
Common exam traps
Common exam trap: answer the scenario, not the keyword
The trap here is that candidates often pick an index starting with OrderDate (Option D) because they think date-range filtering is the primary need, forgetting that the equality filter on CustomerID must be the leading column for an efficient seek.
Detailed technical explanation
How to think about this question
In SQL Server, a composite index stores rows sorted by the leftmost column first, then subsequent columns; for a query with an equality predicate on CustomerID and a range predicate on OrderDate, the index on (CustomerID, OrderDate) allows a single seek to the first matching CustomerID, then a range scan within that segment. The INCLUDE clause adds TotalAmount only at the leaf level, keeping the index narrower and reducing page splits compared to making it a key column. This design is critical for high-volume OLTP workloads where covering indexes eliminate expensive bookmark lookups.
KKey Concepts to Remember
Non-clustered indexes store data separately from the table, containing pointers to the actual data rows.
Composite indexes on multiple columns are most effective when the leading column matches the primary filter in queries.
The `INCLUDE` clause adds non-key columns to the leaf level of a non-clustered index without affecting its key structure.
A 'covering index' contains all columns needed by a query, eliminating the need to access the base table.
TExam Day Tips
→Watch for words such as best, first, most likely and least administrative effort.
→Review why wrong options are wrong, not only why the correct option is correct.
Key takeaway
Non-clustered indexes store data separately from the table, containing pointers to the actual data rows.
Real-world example
How this comes up in practice
A startup's cloud architect reviews their monthly bill and notices costs are higher than expected for a long-running batch job. Switching from on-demand instances to Reserved Instances — or using Spot/Preemptible VMs — can reduce compute costs by up to 72 %. Questions like this test whether you understand the tradeoffs between commitment, flexibility, and cost across cloud pricing models.
Related glossary terms
Concepts from this question explained
These glossary pages explain the core terms tested in this DP-900 question in full detail.
Review non-clustered indexes store data separately from the table, containing pointers to the actual data rows., then practise related DP-900 questions on the same topic to reinforce the concept.
Identify considerations for relational data on Azure — This question tests Identify considerations for relational data on Azure — Non-clustered indexes store data separately from the table, containing pointers to the actual data rows..
What is the correct answer to this question?
The correct answer is: A non-clustered index on (CustomerID, OrderDate) INCLUDE (TotalAmount) — The query filters on CustomerID and OrderDate, so a composite non-clustered index on (CustomerID, OrderDate) allows SQL Server to perform an index seek on both columns, drastically reducing the number of rows scanned. Including TotalAmount as a non-key column makes this a covering index, meaning all needed data (including TotalAmount) is in the index leaf pages, avoiding costly key lookups to the clustered index.
What should I do if I get this DP-900 question wrong?
Review non-clustered indexes store data separately from the table, containing pointers to the actual data rows., then practise related DP-900 questions on the same topic to reinforce the concept.
Are there clue words in this question I should notice?
Yes — watch for: "primary". Asks for the main purpose or function, not a secondary benefit. Eliminate answers that describe side-effects or partial functions.
What is the key concept behind this question?
Non-clustered indexes store data separately from the table, containing pointers to the actual data rows.
About these practice questions
Courseiva creates original exam-style practice questions with explanations and wrong-answer analysis. It does not publish real exam questions, exam dumps, or protected exam content. Learn why practice questions differ from exam dumps →
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
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.
Question Discussion
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
Sign in to join the discussion.