DP-900 Practice Question: Identify considerations for relational data on Azure
A financial application stores transactions in an Azure SQL Database table with columns: TransactionID (clustered index), AccountID, TransactionDate, Amount. Queries frequently filter on AccountID and TransactionDate together. The table contains millions of rows. Which index strategy will most improve query performance for these filters?
⚠ Common exam trap
It's easy for candidates to choose Option A (changing the clustered index) because they think it will be faster for all queries, but they overlook the negative impact on the existing primary key and the fact that a nonclustered covering index is sufficient and less disruptive.
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
✓
Nonclustered index on (AccountID, TransactionDate)
Creates a nonclustered index on (AccountID, TransactionDate) that acts as a covering index for queries filtering on both columns. SQL Server can seek directly to the matching rows using the composite key order, avoiding a full table scan or key lookup. This is the most efficient strategy because the index is sorted by AccountID first, then TransactionDate, matching the query predicate exactly.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Clustered index on (AccountID, TransactionDate)
Why it's wrong here
A table can have only one clustered index because it determines the physical order of rows on disk; this table already has a clustered index on TransactionID. Replacing it with a clustered index on (AccountID, TransactionDate) would force a full rewrite of every data page, causing fragmentation and possible performance degradation for any query that seeks by TransactionID. Moreover, the original clustered index on TransactionID might still be needed for point lookups or range scans by transaction key, so changing it is not a low-risk optimization.
- ✗
Nonclustered index on (TransactionDate)
Why it's wrong here
This nonclustered index is built with TransactionDate as the leading key, so it can efficiently seek or scan by date alone. However, the query filters on both AccountID and TransactionDate, and because AccountID is not part of the index key, SQL Server would have to examine all index entries for the given date range across every account, then apply a residual predicate to isolate the specific account. That approach reads far more index rows than necessary and does not leverage the account restriction to narrow the initial search space, making it suboptimal for account-centric reports.
- ✗
Nonclustered index on (AccountID) INCLUDE (TransactionDate)
Why it's wrong here
With AccountID as the key column and TransactionDate only as an included column, this index can seek directly to the account, but TransactionDate is not part of the index key and therefore cannot be used for a range seek. SQL Server would have to read every index row for that account, compare each TransactionDate against the filter, and then perform key lookups into the clustered index for every row that passes. This results in substantially more I/O than a composite index that places TransactionDate as the second key column, which enables a precise range scan without lookups.
- ✓
Nonclustered index on (AccountID, TransactionDate)
Why this is correct
This composite nonclustered index uses AccountID as the leading key, allowing the query optimizer to seek directly to the index entries for the requested account. Within that account, TransactionDate is the second key column, so the index can perform an ordered range scan to retrieve only the rows whose transaction date falls within the specified window. Because the index contains both columns in its key, it can cover this query without returning to the clustered index, minimizing logical reads and making it the most efficient choice for the described filter.
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
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
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.