DP-900 Practice Question: Identify considerations for relational data on Azure
A healthcare organization stores patient appointment data in an Azure SQL Database. The Appointments table contains columns: AppointmentID (int, primary key), PatientID (int), AppointmentDate (datetime2), and Status (varchar(20)). Most queries filter by AppointmentDate to retrieve appointments for a specific day, and the table currently has no indexes other than the clustered index on AppointmentID. The database administrator needs to improve the performance of these date-based queries while minimizing the impact on insert operations for new appointments. What should the administrator do?
⚠ Common exam trap
The trap here is assuming that any index on AppointmentDate will work, but changing the clustered index can disrupt the primary key and increase insert overhead, while a nonclustered index is the targeted solution.
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 the AppointmentDate column.
The queries frequently filter on AppointmentDate, so an index on that column enables the database engine to locate matching rows efficiently without scanning the whole table. A nonclustered index is preferred because it improves read performance for the date filter while keeping the existing clustered index on AppointmentID intact. It also imposes less disruption on insert operations than changing the clustered index, balancing performance and maintenance.
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 filtered index on the Status column where Status = 'Completed'.
Why it's wrong here
A filtered index on Status would only help queries that filter on that specific status value. The scenario states that most queries filter by AppointmentDate, so this index would not be used for those queries. It also adds overhead for inserts when the status matches the filter. Therefore, it does not address the primary performance issue of date-based filtering.
- ✓
Create a nonclustered index on the AppointmentDate column.
Why this is correct
A nonclustered index on AppointmentDate allows the query optimizer to seek directly to the relevant date range instead of scanning the entire table. Because it is nonclustered, it is stored separately from the table data, so inserts only need to update the index structure for the new row's date value. This balances read performance for date-filtered queries with manageable overhead on inserts, making it the appropriate choice for this scenario.
- ✗
Create a columnstore index on the Appointments table.
Why it's wrong here
A columnstore index is designed for large-scale analytical queries that scan many rows and aggregate columns, not for transactional queries that filter by a specific date and retrieve individual rows. While it can compress data and speed up scans, it is not ideal for this OLTP-style workload and may add complexity for inserts. The scenario calls for efficient date-based seeks, which a rowstore nonclustered index handles better.
- ✗
Create a clustered index on the AppointmentDate column.
Why it's wrong here
Changing the clustered index to AppointmentDate would physically reorder the table by date, which can help range queries, but it would replace the existing clustered index on AppointmentID. This disrupts the primary key's default clustering and may degrade lookups by AppointmentID. Additionally, inserts of appointments with non-sequential dates could cause page splits and fragmentation, increasing maintenance overhead more than a nonclustered index would.
Go deeper
Related to this question
Learn chapter
Indexes, Views, and Stored Procedures
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
Data
Data is raw, unprocessed information, like numbers, words, or measurements, that can be stored, processed, and analyzed by computers.
About these practice questions
One of 851 original DP-900 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Microsoft exam blueprint
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.