Nonclustered Index on Multiple Columns for Azure SQL Database
A logistics company uses Azure SQL Database to store millions of shipment records. The table has columns: ShipmentID (primary key), CustomerID, ShipDate, and Destination. Queries frequently filter by CustomerID and ShipDate to retrieve shipments for a specific customer over a date range. Which indexing strategy will most improve query performance?
Quick Answer
The correct choice is to create a nonclustered index on CustomerID and ShipDate. This composite index directly supports the frequent query pattern of filtering by both columns, enabling the Azure SQL Database engine to perform an index seek rather than a full table scan, which drastically reduces I/O and speeds up retrieval across millions of rows. On the Microsoft Azure Data Fundamentals DP-900 exam, this scenario tests your understanding of indexing strategies for multi-column filters, often appearing as a question where a single-column index on CustomerID alone is a tempting but incomplete answer—it would still require a key lookup for each date range. A common trap is assuming any index on both columns works regardless of column order; remember that the leftmost column in the index definition must match the filter’s most selective column for optimal seek performance. Memory tip: think of a phone book sorted by last name then first name—you can’t efficiently look up all “Johns” without a last name, just as an index on (ShipDate, CustomerID) won’t help if you filter by CustomerID first.
⚠ Common exam trap
Candidates often assume a clustered index on the primary key is always optimal, but for queries that filter on non-key columns, a covering nonclustered index is far more effective.
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 and ShipDate
A nonclustered index on CustomerID and ShipDate is the best choice because it directly supports the frequent query pattern filtering by both columns. This composite index allows SQL Database to perform an index seek rather than a full table scan, drastically reducing I/O for selective queries over millions of rows.
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 nonclustered index on CustomerID and ShipDate
Why this is correct
This composite index covers both filter columns, enabling efficient seek operations for the WHERE clause conditions.
- ✗
Create a clustered index on ShipmentID
Why it's wrong here
A clustered index on the primary key helps for lookups by ShipmentID but does not improve filtering on CustomerID and ShipDate; the query would still require a full scan or separate index.
When this WOULD be correct
If the question asked for the best indexing strategy when queries frequently filter by ShipmentID or need to retrieve a single shipment by its ID, then a clustered index on ShipmentID would be optimal, as it provides fast point lookups.
- ✗
Partition the table by ShipmentID
Why it's wrong here
Partitioning by ShipmentID does not align with the common query filters (CustomerID, ShipDate). Partition elimination would not occur, so performance gains are minimal.
When this WOULD be correct
A question where the table is very large and queries frequently filter or aggregate by ShipmentID ranges, or where data needs to be managed (e.g., archiving old shipments) by ShipmentID ranges.
- ✗
Create a full-text index on Destination
Why it's wrong here
Full-text indexes are designed for searching words or phrases in text columns, not for range or equality queries on CustomerID and ShipDate.
When this WOULD be correct
A full-text index on Destination would be correct if the question involved searching for shipments based on keywords or phrases in the destination column, such as 'Find all shipments to cities containing 'Springfield' or 'New York'.
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.
✓Create a nonclustered index on CustomerID and ShipDateCorrect answer▾
Why this is correct
This composite index covers both filter columns, enabling efficient seek operations for the WHERE clause conditions.
✗Create a clustered index on ShipmentIDWrong answer — click to see why▾
Why this is wrong here
A clustered index on ShipmentID (the primary key) is already the default, but queries filter by CustomerID and ShipDate, not ShipmentID. This index does not support the filtering columns, so it won't improve performance for the specified queries.
★ When this WOULD be the correct answer
If the question asked for the best indexing strategy when queries frequently filter by ShipmentID or need to retrieve a single shipment by its ID, then a clustered index on ShipmentID would be optimal, as it provides fast point lookups.
Why candidates choose this
Candidates may assume that indexing the primary key is always beneficial, or they may not realize that a clustered index on the primary key already exists by default, making this option redundant for the given query pattern.
✗Partition the table by ShipmentIDWrong answer — click to see why▾
Why this is wrong here
Partitioning by ShipmentID does not help queries filtering by CustomerID and ShipDate because the partition key is not used in the WHERE clause, so all partitions must be scanned.
★ When this WOULD be the correct answer
A question where the table is very large and queries frequently filter or aggregate by ShipmentID ranges, or where data needs to be managed (e.g., archiving old shipments) by ShipmentID ranges.
Why candidates choose this
Candidates may think partitioning always improves query performance for any filter, not realizing that the partition key must align with the query filter to be effective.
✗Create a full-text index on DestinationWrong answer — click to see why▾
Why this is wrong here
A full-text index on Destination is designed for text search (e.g., finding words or phrases in a string), not for filtering on exact values like CustomerID and ShipDate. It does not support range queries or equality filters efficiently, so it won't improve performance for the described queries.
★ When this WOULD be the correct answer
A full-text index on Destination would be correct if the question involved searching for shipments based on keywords or phrases in the destination column, such as 'Find all shipments to cities containing 'Springfield' or 'New York'.
Why candidates choose this
Candidates might think any index on a frequently queried column helps, or they may confuse full-text indexing with regular indexing, assuming it can speed up all types of queries on that column.
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
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
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 →
Same concept, more angles
1 more way this is tested on DP-900
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. 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?
medium- ✓ A.Create a non-clustered index on OrderDate and CustomerID
- B.Create a clustered index on OrderDate
- C.Create a non-clustered index on OrderID
- D.Partition the table by CustomerID
Why A: 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.
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.