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

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 Timestamp only

  Why it's wrong here

  A non-clustered index on Timestamp alone is insufficient for the typical IoT query pattern because the filter is usually on DeviceID first, then a time range. Without DeviceID as the leading key column, the index cannot perform a seek to isolate a specific device's rows; instead, the query optimizer would have to scan a large portion of the index across all timestamps and then apply a residual predicate, resulting in significant I/O and key lookups. The index is optimized for global time-range scans, not for per-device time slices, making it a poor fit for this workload.

• ✓

  Create a composite index on (DeviceID, Timestamp)

  Why this is correct

  A composite index on (DeviceID, Timestamp) directly matches the query's equality predicate on DeviceID and range predicate on Timestamp. The leftmost prefix rule lets the optimizer seek to the exact DeviceID value and then use the Timestamp column to efficiently navigate the range, retrieving only the relevant rows in sorted order. This minimizes both the number of index pages read and the associated key lookups, making it the ideal choice for high-volume IoT data where per-device temporal queries are frequent.

• ✗

  Create a clustered index on DeviceID only

  Why it's wrong here

  A clustered index on DeviceID only would physically organize all rows for a single device together, which is beneficial for record-level retrieval, but it does not impose any logical or physical ordering on the Timestamp column within each device's data. Consequently, a time-range query for a specific device would require scanning every row belonging to that device to filter by Timestamp, which becomes increasingly expensive as the number of readings per device grows. Furthermore, because a clustered index is the table itself, this structure lacks a separate key that can efficiently support range scans, so it fails to optimize the time dimension of the query.

• ✗

  Create a non-clustered index on SensorType

  Why it's wrong here

  SensorType is not part of the query's WHERE clause or ORDER BY, so creating an index on that column would not be used by the optimizer for this workload. The index would occupy extra storage, increase write amplification during data ingestion, and provide no query performance benefit for the DeviceID and Timestamp predicates. It addresses an access path that is irrelevant to the stated IoT query pattern, making it an unnecessary and potentially harmful design choice in terms of cost and maintenance overhead.

Go deeper

Related to this question