DP-900 Practice Question: Identify considerations for relational data on Azure
A hospital uses Azure SQL Database to store patient appointment records. The 'Appointments' table has columns: AppointmentID (primary key), PatientID, DoctorID, AppointmentDate, and Status. Queries frequently filter by DoctorID (equality) and AppointmentDate (range) to retrieve a doctor's schedule. Currently, these queries are slow. Which index strategy will most improve performance for these queries?
⚠ Common exam trap
Many exam-takers think the date column should be first because it's a range query, but the correct strategy is to place the equality column first to minimize the scan range, then the range column second for efficient filtering.
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
✓
Add a nonclustered index on (DoctorID, AppointmentDate).
A nonclustered index on (DoctorID, AppointmentDate) supports both equality filtering on DoctorID and range filtering on AppointmentDate. This index structure allows SQL Server to perform a single index seek for the doctor, then a range scan within that doctor's appointments, avoiding a full table scan. The order of columns matters: the leading column (DoctorID) handles the equality predicate, and the second column (AppointmentDate) handles the range predicate efficiently.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Add a clustered index on AppointmentID.
Why it's wrong here
A clustered index on AppointmentID is typically the primary key index, but this query's WHERE clause does not reference AppointmentID, so that index cannot provide a seek key. Without a supporting nonclustered index on DoctorID and AppointmentDate, SQL Server would have to scan the entire clustered index (every appointment row) and evaluate the filter after reading each row. While a clustered index determines the physical order of the table, it does not help for filters on non-key columns unless it is used as a covering index with included columns.
When this WOULD be correct
This would be correct if the question asked for the best index to support point lookups by AppointmentID, e.g., 'Which index improves performance for queries that retrieve a single appointment by its ID?'
- ✓
Add a nonclustered index on (DoctorID, AppointmentDate).
Why this is correct
This composite index is correctly ordered for the query filter because DoctorID is used with an equality predicate, so SQL Server can perform a seek directly to that doctor's rows. The subsequent key column, AppointmentDate, is used with a range predicate, and once the seek lands on the doctor, scanning the date range is a narrow, contiguous index scan. This maximizes selectivity and minimizes the number of index rows touched compared to the reversed column order.
- ✗
Add a columnstore index on the Status column.
Why it's wrong here
Columnstore indexes are optimized for analytical workloads that scan large numbers of rows and perform aggregation; they are not effective for OLTP queries that filter by equality and range on a small subset of rows. Indexing only the Status column—especially when Status is likely a low-cardinality flag like 'scheduled' or 'completed'—would require reading many compressed row segments and still would not produce an efficient seek. A rowstore nonclustered index with key columns DoctorID and AppointmentDate is the appropriate structure for this highly selective point and range lookup.
When this WOULD be correct
This option would be correct if the question asked for improving performance of aggregate queries like 'SELECT COUNT(*), Status FROM Appointments GROUP BY Status' over a large table, where columnstore indexes provide high compression and fast aggregation.
- ✗
Add a nonclustered index on (AppointmentDate, DoctorID).
Why it's wrong here
Placing AppointmentDate first is suboptimal because a range predicate on the leading column prevents SQL Server from using an equality seek on DoctorID as the primary navigation path. The engine can only seek to the beginning of the date range and then must scan every appointment in that window, evaluating DoctorID as a residual predicate for each row. Because the date range could span many doctors and a large number of appointments, this reversed order can require significantly more index reads than the correct (DoctorID, AppointmentDate) ordering.
When this WOULD be correct
This index would be correct if queries frequently filter by AppointmentDate (range) first and then by DoctorID (equality), e.g., 'Find all appointments on a given date for a specific doctor.'
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.
✓Add a nonclustered index on (DoctorID, AppointmentDate).Correct answer▾
Why this is correct
This composite index is correctly ordered for the query filter because DoctorID is used with an equality predicate, so SQL Server can perform a seek directly to that doctor's rows. The subsequent key column, AppointmentDate, is used with a range predicate, and once the seek lands on the doctor, scanning the date range is a narrow, contiguous index scan. This maximizes selectivity and minimizes the number of index rows touched compared to the reversed column order.
✗Add a clustered index on AppointmentID.Wrong answer — click to see why▾
Why this is wrong here
A clustered index on AppointmentID optimizes lookups by primary key but does not support the query predicate on DoctorID and AppointmentDate, so the queries will still require a full scan or key lookup for each row.
★ When this WOULD be the correct answer
This would be correct if the question asked for the best index to support point lookups by AppointmentID, e.g., 'Which index improves performance for queries that retrieve a single appointment by its ID?'
Why candidates choose this
Candidates often assume the primary key index is always the best choice, overlooking that query performance depends on the filter columns used in WHERE clauses.
✗Add a columnstore index on the Status column.Wrong answer — click to see why▾
Why this is wrong here
A columnstore index on Status does not support the query pattern of filtering by DoctorID and AppointmentDate; columnstore indexes are optimized for large-scale analytical aggregations, not for point lookups or range scans on specific columns.
★ When this WOULD be the correct answer
This option would be correct if the question asked for improving performance of aggregate queries like 'SELECT COUNT(*), Status FROM Appointments GROUP BY Status' over a large table, where columnstore indexes provide high compression and fast aggregation.
Why candidates choose this
Candidates may think columnstore indexes are a modern performance feature applicable to any slow query, without understanding they are designed for data warehousing and analytical workloads, not for transactional queries with selective filters.
✗Add a nonclustered index on (AppointmentDate, DoctorID).Wrong answer — click to see why▾
Why this is wrong here
The index on (AppointmentDate, DoctorID) is less effective because the query filters by DoctorID first (equality) and then AppointmentDate (range). With this column order, the range scan on AppointmentDate may include many rows before filtering by DoctorID, reducing performance.
★ When this WOULD be the correct answer
This index would be correct if queries frequently filter by AppointmentDate (range) first and then by DoctorID (equality), e.g., 'Find all appointments on a given date for a specific doctor.'
Why candidates choose this
Candidates may think that placing the range column first is acceptable, but they overlook that equality predicates should lead for optimal index seek performance.
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
Column
A column is a vertical set of values in a database table that stores one specific type of attribute for every row.
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.