DP-900 Practice Question: Identify considerations for relational data on Azure
A company uses Azure SQL Database for an order management system. The 'Orders' table has millions of rows and is queried frequently with filters on OrderDate and CustomerID. The table currently has a clustered index on OrderID. Which action will most improve query performance for these frequent filters?
⚠ Common exam trap
Many exam-takers assume partitioning alone solves query performance issues, but without an appropriate index, partitioning only helps with data management and partition elimination, not with efficient row-level filtering for specific column combinations.
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 non-clustered index on OrderDate and CustomerID
The frequent filters on OrderDate and CustomerID require a covering index that includes both columns. A non-clustered index on (OrderDate, CustomerID) allows SQL Server to perform an index seek for queries filtering on those columns, avoiding full clustered index scans on the existing clustered index on OrderID. This directly reduces I/O and improves query response times.
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 non-clustered index on OrderDate and CustomerID
Why this is correct
A non-clustered index on OrderDate and CustomerID directly supports the WHERE clauses that filter orders by date ranges and customer lookups. The index structure allows the query engine to perform an index seek rather than a full table scan, dramatically reducing I/O. Including both columns lets the engine satisfy equality on CustomerID and range on OrderDate efficiently, and the index can even cover some queries if only those columns are needed.
- ✗
Create a clustered index on OrderDate
Why it's wrong here
Incorrect. Changing the clustered index to OrderDate might help range queries on OrderDate but could disrupt other queries that rely on OrderID for sorting or joins. Also, it does not directly help with CustomerID filters.
- ✗
Create a non-clustered index on OrderID
Why it's wrong here
Since OrderID is the clustered index key, the table data is already physically ordered by OrderID, making an additional non-clustered index on OrderID redundant for that key. The non-clustered index would store a separate copy of OrderID plus a pointer to each row, adding write overhead and storage without helping queries that filter on OrderDate or CustomerID. Queries that seek on OrderID already use the clustered index; the extra index only helps if it includes additional columns as included columns, which is not the case here.
- ✗
Partition the table by CustomerID
Why it's wrong here
Incorrect. Partitioning can improve management and possibly query performance for large tables, but without appropriate indexes, queries still need to scan partitions. An index on the filter columns is more effective for these specific queries.
Go deeper
Related to this question
Learn chapter
Azure SQL Services
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.
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.
About these practice questions
One of 820 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 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.