DP-900 Practice Question: Identify considerations for relational data on Azure
A company uses Azure SQL Database to store a large table of sales transactions with columns: TransactionID (primary key), CustomerID, ProductID, SaleDate, Amount. Queries frequently filter by both CustomerID and SaleDate to retrieve sales for a specific customer over a date range. Which indexing strategy will most improve query performance?
⚠ Common exam trap
It's easy for candidates to choose Option D (SaleDate, CustomerID) thinking the date range should be first, but they overlook that the equality filter on CustomerID should be the leading column to enable a seek, not a scan.
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 (CustomerID, SaleDate)
Creating a nonclustered index on (CustomerID, SaleDate) as a composite index directly supports the query predicate that filters by both CustomerID and SaleDate. The index is ordered by CustomerID first, enabling efficient seeks for a specific customer, and then by SaleDate within each customer, allowing the query engine to perform a range scan for the date range without scanning the entire table or sorting. This index is a covering index for this query, as it contains all columns needed for the filter, avoiding key lookups.
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 SaleDate
Why it's wrong here
A clustered index on SaleDate alone forces the entire table's physical row order to be date-based, but the query predicates are driven by CustomerID, not by date. To find a specific customer's sales, SQL Server would need to scan through many date ranges because the index does not include CustomerID as a key or included column, so there is no direct path to locate that customer's rows. Furthermore, since a table can have only one clustered index, choosing SaleDate here would sacrifice the clustered index's benefit for the more common customer-first access pattern.
When this WOULD be correct
If queries frequently filter only by SaleDate (e.g., retrieving all sales for a date range) and the table is large, a clustered index on SaleDate would improve range scan performance.
- ✗
Create a nonclustered index on CustomerID and include SaleDate
Why it's wrong here
Including SaleDate as an included column means the index key is only CustomerID. The index can find rows for a CustomerID, but it cannot use SaleDate for range filtering; it would have to scan all matching rows.
When this WOULD be correct
If queries always filter by CustomerID and then retrieve SaleDate (but never filter or sort by SaleDate), a nonclustered index on CustomerID with SaleDate as an included column would be optimal because it covers the query without needing to order by SaleDate.
- ✓
Create a nonclustered index on (CustomerID, SaleDate)
Why this is correct
A nonclustered index with CustomerID as the leading key and SaleDate as the second key lets the query engine perform an index seek on CustomerID (equality) and then efficiently navigate the ordered SaleDate values within that customer's rows for range filtering. Because the index entries are sorted by CustomerID first and SaleDate second, the engine can stop scanning as soon as the date condition is met for that customer. This index also covers queries that only return CustomerID and SaleDate, avoiding costly key lookups to the clustered index or heap.
- ✗
Create a nonclustered index on (SaleDate, CustomerID)
Why it's wrong here
Although this composite index contains both relevant columns, listing SaleDate before CustomerID inverts the key order for this query. Since the query filters on CustomerID first, the leftmost prefix rule means the engine cannot seek on CustomerID; instead, it would have to scan the index across many SaleDate values and then apply a residual predicate to match CustomerID. For a large table, this index is nearly as ineffective as an index on SaleDate alone, because the leading column does not correspond to the equality condition in the WHERE clause.
When this WOULD be correct
If the query pattern were to filter primarily by SaleDate (e.g., retrieve all sales for a date range) and only secondarily by CustomerID, or if the query used a range on SaleDate without equality on CustomerID, then (SaleDate, CustomerID) would be the better index.
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 nonclustered index on (CustomerID, SaleDate)Correct answer▾
Why this is correct
A nonclustered index with CustomerID as the leading key and SaleDate as the second key lets the query engine perform an index seek on CustomerID (equality) and then efficiently navigate the ordered SaleDate values within that customer's rows for range filtering. Because the index entries are sorted by CustomerID first and SaleDate second, the engine can stop scanning as soon as the date condition is met for that customer. This index also covers queries that only return CustomerID and SaleDate, avoiding costly key lookups to the clustered index or heap.
✗Create a clustered index on SaleDateWrong answer — click to see why▾
Why this is wrong here
A clustered index on SaleDate alone does not support the equality filter on CustomerID, so queries filtering by both CustomerID and SaleDate would require a full scan or inefficient lookup for CustomerID.
★ When this WOULD be the correct answer
If queries frequently filter only by SaleDate (e.g., retrieving all sales for a date range) and the table is large, a clustered index on SaleDate would improve range scan performance.
Why candidates choose this
Candidates may think a clustered index on a frequently filtered column always improves performance, ignoring that the query also filters by CustomerID, which is not covered.
✗Create a nonclustered index on CustomerID and include SaleDateWrong answer — click to see why▾
Why this is wrong here
Including SaleDate as an included column (not a key column) means the index is ordered by CustomerID only, so it cannot efficiently support range queries on SaleDate for a given customer; the database would still need to scan all rows for that CustomerID to filter by date range.
★ When this WOULD be the correct answer
If queries always filter by CustomerID and then retrieve SaleDate (but never filter or sort by SaleDate), a nonclustered index on CustomerID with SaleDate as an included column would be optimal because it covers the query without needing to order by SaleDate.
Why candidates choose this
Candidates may think that including SaleDate in the leaf level is sufficient for date filtering, not realizing that without SaleDate as a key column, the index cannot perform efficient range seeks on the date column.
✗Create a nonclustered index on (SaleDate, CustomerID)Wrong answer — click to see why▾
Why this is wrong here
For queries filtering by both CustomerID and SaleDate, a composite index on (CustomerID, SaleDate) is optimal because it allows equality on CustomerID and range on SaleDate. The index on (SaleDate, CustomerID) would require scanning all rows for a given date range before filtering by CustomerID, which is less efficient.
★ When this WOULD be the correct answer
If the query pattern were to filter primarily by SaleDate (e.g., retrieve all sales for a date range) and only secondarily by CustomerID, or if the query used a range on SaleDate without equality on CustomerID, then (SaleDate, CustomerID) would be the better index.
Why candidates choose this
Candidates may think that since SaleDate is used in a range query, it should be the leading column, but they overlook that equality conditions (CustomerID) should come first in a composite index for optimal seek operations.
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
Azure SQL Database
Azure SQL Database is a fully managed relational database-as-a-service (DBaaS) in Microsoft Azure, based on the SQL Server engine, that handles scaling, backups, patching, and high availability automatically.
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.