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 transaction date column

  Why it's wrong here

  A clustered index on transaction_date physically orders rows by date, so a range scan for a month is fast, but the business analyst needs to aggregate (SUM) by product category across potentially millions of rows. The query still has to read every row in that date range, and because the index is row-structured, SQL Server cannot use columnstore compression or batch-mode (vectorized) execution to reduce I/O. For heavy analytical aggregations over a large fact table, this index is insufficient because it does not address the high cost of scanning and aggregating all category values.

• ✗

  Create a nonclustered index on the product category column

  Why it's wrong here

  A nonclustered index on product_category is a B-tree that speeds up equality and range filters on category, but the query's primary predicate is the transaction date (e.g., last month) and the aggregation spans all categories. To compute per-category sums for the month, SQL Server would still effectively scan the entire category index or the whole table, and if the index is not covering (does not include transaction_date and amount), it adds expensive key lookups. Rowstore indexes also lack column-level compression and batch-mode aggregation, so they do not scale well for this analytical workload.

• ✓

  Create a columnstore index on the table

  Why this is correct

  A columnstore index stores each column as a separate, compressed segment and enables SQL Server to read only the columns needed for the query (e.g., amount, category, date) rather than whole rows. It uses batch-mode processing, where the engine processes data in large batches with vectorized operators, dramatically accelerating SUM and GROUP BY over large tables. For a large Azure SQL Database table used in analytical queries, a clustered columnstore index is purpose-built for this exact scenario and requires no query rewrites.

• ✗

  Create a filtered index on transactions from the current month

  Why it's wrong here

  A filtered index on transactions from the current month is a nonclustered index with a WHERE clause that limits rows to a sliding window, making it valid only for that specific period. Once the month changes, the index becomes stale and must be dropped/rebuilt or updated with every new transaction, and queries for prior months cannot use it at all. Even for the current month, it uses row-oriented storage and lacks columnstore compression/batch mode, so aggregating by category across all rows in that filtered subset is still comparatively slow and I/O-heavy for a large transactional table.

Go deeper

Related to this question