Courseiva
← DP-900
Full test
Identify considerations for relational data on Azure →mediumMultiple ChoiceObjective-mapped

DP-900 Practice Question: Identify considerations for relational data on Azure

A company has a table named 'Sales' in Azure SQL Database with columns: SaleID (int, primary key), ProductID (int), SaleDate (datetime), Quantity (int), UnitPrice (decimal), TotalAmount (computed column). Queries frequently run to retrieve the total Quantity and UnitPrice for a specific ProductID over a date range. The query filters on ProductID and SaleDate and selects only Quantity and UnitPrice. Which index would most improve query performance?

⚠ Common exam trap

Many candidates think a clustered index on the filter columns is always best, but they overlook that a nonclustered index with included columns can provide a covering index that avoids costly key lookups, especially when the SELECT list is a subset of columns.

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 (ProductID, SaleDate) INCLUDE (Quantity, UnitPrice)

It creates a covering nonclustered index that supports both the WHERE clause (ProductID, SaleDate) and the SELECT clause (Quantity, UnitPrice) without needing to access the base table. The index key order matches the query filter, and the included columns avoid key lookups, minimizing I/O for the frequent aggregation queries.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

  • ✓

    Nonclustered index on (ProductID, SaleDate) INCLUDE (Quantity, UnitPrice)

    Why this is correct

    This covering index includes all columns needed by the query (Quantity, UnitPrice) as included columns, and the key columns (ProductID, SaleDate) support efficient filtering. The query can be satisfied entirely from the index without key lookups.

  • ✗

    Nonclustered index on (SaleDate) INCLUDE (Quantity, UnitPrice)

    Why it's wrong here

    This index has SaleDate as the key, but the query also filters on ProductID. Without ProductID as a key column, the database may need to scan many rows for each date range, leading to poor performance.

    When this WOULD be correct

    If the query filtered only on SaleDate (e.g., total Quantity and UnitPrice for all products over a date range), this index would be correct because it supports seeking on SaleDate and includes the needed columns.

  • ✗

    Clustered index on (ProductID, SaleDate)

    Why it's wrong here

    A clustered index defines the physical order and includes all columns, but changing the clustered index order would require reordering the entire table, which may cause fragmentation and impact other queries. Additionally, a covering nonclustered index is often lighter for this specific query.

    When this WOULD be correct

    If the query required all columns from the Sales table (e.g., SELECT *), a clustered index on (ProductID, SaleDate) would be optimal because it includes all columns without additional lookups. This is common in queries that retrieve full rows for reporting or analysis.

  • ✗

    Nonclustered index on (ProductID) INCLUDE (Quantity, UnitPrice)

    Why it's wrong here

    This index includes ProductID as the key, but the query also filters on SaleDate. Without SaleDate in the key, the database would need to scan all rows for the given ProductID and then apply a residual predicate on SaleDate, which is less efficient.

    When this WOULD be correct

    If the query only filters on ProductID (no date range) and selects Quantity and UnitPrice, this covering index would be optimal. For example: 'SELECT Quantity, UnitPrice FROM Sales WHERE ProductID = ?'

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.

✓Nonclustered index on (ProductID, SaleDate) INCLUDE (Quantity, UnitPrice)Correct answer▾

Why this is correct

This covering index includes all columns needed by the query (Quantity, UnitPrice) as included columns, and the key columns (ProductID, SaleDate) support efficient filtering. The query can be satisfied entirely from the index without key lookups.

✗Nonclustered index on (SaleDate) INCLUDE (Quantity, UnitPrice)Wrong answer — click to see why▾

Why this is wrong here

This index does not include ProductID as the leading key, so queries filtering on both ProductID and SaleDate cannot seek on ProductID first; they may scan or seek on SaleDate only, missing the optimal key order for the query's filter.

★ When this WOULD be the correct answer

If the query filtered only on SaleDate (e.g., total Quantity and UnitPrice for all products over a date range), this index would be correct because it supports seeking on SaleDate and includes the needed columns.

Why candidates choose this

Candidates may think that since SaleDate is used in the filter, it should be the index key, overlooking that the query also filters on ProductID and that leading with ProductID is more selective.

✗Clustered index on (ProductID, SaleDate)Wrong answer — click to see why▾

Why this is wrong here

A clustered index on (ProductID, SaleDate) would physically order the table by those columns, but the query selects only Quantity and UnitPrice, which are not included in the index key. This forces key lookups to retrieve those columns, reducing performance compared to a covering nonclustered index.

★ When this WOULD be the correct answer

If the query required all columns from the Sales table (e.g., SELECT *), a clustered index on (ProductID, SaleDate) would be optimal because it includes all columns without additional lookups. This is common in queries that retrieve full rows for reporting or analysis.

Why candidates choose this

Candidates may think a clustered index is always faster for range queries, or they may confuse the covering index concept, assuming that clustering on the filter columns automatically includes all data.

✗Nonclustered index on (ProductID) INCLUDE (Quantity, UnitPrice)Wrong answer — click to see why▾

Why this is wrong here

This index does not include SaleDate, so it cannot efficiently support the date range filter. The query would still need to scan all rows for the given ProductID to find those within the date range.

★ When this WOULD be the correct answer

If the query only filters on ProductID (no date range) and selects Quantity and UnitPrice, this covering index would be optimal. For example: 'SELECT Quantity, UnitPrice FROM Sales WHERE ProductID = ?'

Why candidates choose this

Candidates may think that filtering on ProductID alone is sufficient, overlooking the date range filter. They might also assume that including the selected columns makes the index covering, ignoring the missing filter 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?”

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 →

How Courseiva writes practice questions · Editorial policy

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.