DP-900 Practice Question: Identify considerations for relational data on Azure
A retail company uses Azure SQL Database for an order management system. The Orders table has columns: OrderID (primary key, clustered), CustomerID, OrderDate, TotalAmount. Queries frequently filter on CustomerID and OrderDate, and sort results by OrderDate in descending order. The queries also return the TotalAmount. Which indexing strategy will most improve query performance for these operations?
⚠ Common exam trap
Microsoft often tests the distinction between covering indexes and columnstore indexes, and the trap here is assuming a columnstore index is appropriate for transactional queries with filtering and sorting, when it is actually designed for large-scale analytics and data warehousing workloads.
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, OrderDate DESC) INCLUDE (TotalAmount).
It creates a covering nonclustered index that supports both the filter predicates (CustomerID and OrderDate) and the sort order (OrderDate DESC) while including TotalAmount as an included column to avoid key lookups. This index allows SQL Server to satisfy the query entirely from the index pages, minimizing I/O and improving performance.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Maintain the existing clustered index on OrderID only.
Why it's wrong here
The existing clustered index on OrderID only accelerates queries that locate rows by primary key; it cannot satisfy filters on CustomerID or OrderDate. Because the leaf level is ordered solely by OrderID, any request for a specific customer within a date range forces a full clustered index scan followed by a sort for ORDER BY OrderDate DESC. A narrow, query-specific nonclustered index with the right key columns and included TotalAmount is required.
- ✓
Create a nonclustered index on (CustomerID, OrderDate DESC) INCLUDE (TotalAmount).
Why this is correct
This index is ordered by CustomerID then OrderDate descending, allowing efficient seeks for a specific CustomerID and range scans over OrderDate in descending order. Including TotalAmount covers the SELECT clause without needing to access the base table.
- ✗
Create a nonclustered index on (OrderDate DESC) INCLUDE (CustomerID, TotalAmount).
Why it's wrong here
Placing OrderDate as the leading key column prevents the optimizer from using an equality seek on CustomerID, because the index is ordered by date first and customer second. Even if the date range is restricted, every row in that range must be scanned to test the CustomerID predicate, and CustomerID appears only in the INCLUDE list so it cannot act as a seek key. This design also leaves ORDER BY OrderDate DESC only partially optimized while the customer filter remains unresolved.
- ✗
Create a clustered columnstore index on the entire table.
Why it's wrong here
A clustered columnstore index stores each column separately and uses heavy compression, which makes it powerful for analytic scans over large volumes but poorly suited to this OLTP-style request that fetches a single customer's small set of orders. There is no traditional B-tree ordering to quickly navigate to a specific CustomerID and adjacent OrderDate values; instead, column segments may need to be scanned and decompressed. Transactional inserts and updates are also more expensive under columnstore maintenance, so it would degrade the order management workload.
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
Table
A table is a structured collection of data organized into rows and columns, used in databases and spreadsheets to store and manage information efficiently.
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.