DP-900 Practice Question: Identify considerations for relational data on Azure
A retail company uses Azure SQL Database to store a large fact table of sales transactions with millions of rows. They run complex aggregate queries (SUM, COUNT, AVG) across many rows for monthly reports. These queries take too long. Which index type should they add to the table to improve performance?
⚠ Common exam trap
A common mix-up: candidates confuse nonclustered columnstore indexes with clustered columnstore indexes, assuming any columnstore index will suffice, but only the clustered version is designed for large fact tables with heavy aggregation workloads and avoids the overhead of maintaining a separate rowstore index.
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
✓
Clustered columnstore index
Clustered columnstore indexes are optimized for large fact tables and analytical workloads because they store data in a columnar format, which significantly reduces the amount of data read from disk for aggregate queries like SUM, COUNT, and AVG. This index type also uses batch processing and compression to accelerate query performance on millions of rows, making it ideal for monthly reporting 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.
- ✗
Clustered B-tree index
Why it's wrong here
A clustered B-tree index stores the table as row-based pages ordered by the clustering key, making it ideal for point lookups and narrow range scans. For a large fact table, however, aggregation queries must scan virtually all rows and read every column in each row, even when only a few columns are needed, resulting in high logical I/O and poor compression. This row-oriented layout lacks the columnar scan efficiency and batch-mode execution that analytical workloads require.
When this WOULD be correct
A question where the table is frequently queried for individual row lookups or small range scans (e.g., 'Find the total sales for a specific product on a given date') and the table has high write activity would make a clustered B-tree index the correct choice.
- ✗
Nonclustered rowstore index
Why it's wrong here
A nonclustered rowstore index is a separate B-tree structure on selected key columns, commonly used to accelerate equality predicates, joins, or narrowly focused covered queries. It does not change the underlying heap or clustered index, so large-scale aggregations either scan the full rowstore or use the index only when it contains all columns required, which is rare for a wide fact table. Because it preserves row-oriented storage, it cannot eliminate columns at read time or provide columnar compression.
When this WOULD be correct
A nonclustered rowstore index would be correct when queries involve searching for specific rows (e.g., WHERE clause on indexed columns) or when the table is used for OLTP workloads with many point lookups and updates, not for large-scale aggregations.
- ✓
Clustered columnstore index
Why this is correct
As the table's primary storage structure, a clustered columnstore index organizes data column-wise, so an aggregation query reads only the needed column segments. This design delivers high compression and batch-mode processing, which drastically reduces I/O and CPU for full-table scans and GROUP BY operations on large fact tables. It is the default recommended indexing strategy for analytical and data warehousing workloads in Azure SQL Database.
- ✗
Nonclustered columnstore index
Why it's wrong here
A nonclustered columnstore index is a secondary columnar index built on top of a rowstore table, so every insert and update must maintain both structures. For a large fact table, the underlying rowstore still must be accessed for any column not stored in the columnstore, which undermines the columnar benefit and adds storage and transaction overhead. Because the question asks for the primary analytical structure, a clustered columnstore is the better choice for full-table aggregate workloads.
When this WOULD be correct
A question where the table already has a clustered rowstore index (e.g., a primary key) and you need to add a columnstore index for analytics on a subset of columns without restructuring the table. For example: 'You have a large fact table with a clustered index on OrderID. You need to improve performance of aggregate queries on SalesAmount and Quantity columns. Which index should you add?'
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.
✓Clustered columnstore indexCorrect answer▾
Why this is correct
As the table's primary storage structure, a clustered columnstore index organizes data column-wise, so an aggregation query reads only the needed column segments. This design delivers high compression and batch-mode processing, which drastically reduces I/O and CPU for full-table scans and GROUP BY operations on large fact tables. It is the default recommended indexing strategy for analytical and data warehousing workloads in Azure SQL Database.
✗Clustered B-tree indexWrong answer — click to see why▾
Why this is wrong here
A clustered B-tree index organizes data in sorted order, which is efficient for point lookups and range scans but not for large aggregations on many rows. For complex aggregate queries scanning millions of rows, a columnstore index provides much better compression and batch processing, reducing I/O and CPU time.
★ When this WOULD be the correct answer
A question where the table is frequently queried for individual row lookups or small range scans (e.g., 'Find the total sales for a specific product on a given date') and the table has high write activity would make a clustered B-tree index the correct choice.
Why candidates choose this
Candidates may assume that any index improves query performance, and since clustered indexes are common, they might think it's the default best choice without considering the specific workload of large aggregations.
✗Nonclustered rowstore indexWrong answer — click to see why▾
Why this is wrong here
For complex aggregate queries on a large fact table, a nonclustered rowstore index does not provide the columnar storage and batch processing that columnstore indexes offer, so it will not significantly improve performance for SUM, COUNT, AVG across millions of rows.
★ When this WOULD be the correct answer
A nonclustered rowstore index would be correct when queries involve searching for specific rows (e.g., WHERE clause on indexed columns) or when the table is used for OLTP workloads with many point lookups and updates, not for large-scale aggregations.
Why candidates choose this
Candidates may think any index speeds up queries, and nonclustered indexes are common for covering queries, but they overlook that columnstore indexes are specifically designed for analytics and aggregation workloads.
✗Nonclustered columnstore indexWrong answer — click to see why▾
Why this is wrong here
For complex aggregate queries over millions of rows, a clustered columnstore index is optimal. A nonclustered columnstore index would require the base table to have a clustered index, adding overhead, and may not be as efficient for full-table scans needed for aggregates.
★ When this WOULD be the correct answer
A question where the table already has a clustered rowstore index (e.g., a primary key) and you need to add a columnstore index for analytics on a subset of columns without restructuring the table. For example: 'You have a large fact table with a clustered index on OrderID. You need to improve performance of aggregate queries on SalesAmount and Quantity columns. Which index should you add?'
Why candidates choose this
Candidates may think a nonclustered columnstore index is sufficient because it still uses columnar storage, but they overlook that it requires a clustered index on the base table and may not be as performant for full-table scans as a clustered columnstore.
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
Data Roles and Core Concepts
Key term
Data
Data is raw, unprocessed information, like numbers, words, or measurements, that can be stored, processed, and analyzed by computers.
Key term
Batch processing
Batch processing is a method of running high-volume, repetitive data jobs where a group of transactions is collected, processed together automatically, and then results are produced without real-time user interaction.
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 →
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.