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 clustered index on the SessionID column

  Why it's wrong here

  A clustered index physically orders the entire table by the SessionID column, so it is highly useful only when queries continuously seek on SessionID alone. In this workload, the WHERE clauses filter on UserID and LastActivityTime, meaning the index on SessionID cannot directly satisfy those predicates. The query engine would still have to scan all clustered-index rows or rely on a separate nonclustered index, incurring extra I/O and defeating the purpose of the clustered index.

• ✗

  Create a view that filters the data

  Why it's wrong here

  A standard (non-indexed) view is merely a saved query definition; it does not physically store or pre-aggregate any data. Executing a query against it still reads the same underlying tables, so the performance characteristics are no better than running the underlying SELECT directly. An indexed (materialized) view could help, but it requires a unique clustered index and does not necessarily target the UserID and LastActivityTime filters as effectively as a purpose-built nonclustered index.

• ✓

  Create a nonclustered index on UserID and LastActivityTime

  Why this is correct

  A composite nonclustered index on (UserID, LastActivityTime) is precisely tailored for queries that filter on those two columns, often with UserID as an equality predicate and LastActivityTime as a range predicate. The index's B-tree structure lets SQL Server perform an index seek directly to the relevant rows, significantly reducing logical I/O compared to a full table scan. Because UserID is the leading column, it supports point lookups, while LastActivityTime handles ordering or upper/lower bound filters, making it the optimal, low-cost choice for these access patterns.

• ✗

  Partition the table by month

  Why it's wrong here

  Table partitioning by month divides rows into separate physical segments based on the partitioning column, which is primarily a data-management technique for chores like archiving, purging, or sliding-window loads. It does not create a new index on UserID or alter the indexing strategy; queries that filter on UserID and LastActivityTime may still require scanning across many partitions. Partition elimination can only occur when the query predicate directly aligns with partition boundaries, and since UserID is not the partition key, this index-free approach rarely reduces I/O or improves query latency.