DP-900 Practice Question: Identify considerations for relational data on Azure
A retail company uses Azure SQL Database for its sales transaction table, which contains over 500 million rows. Queries that filter by OrderDate are slow because the database scans the entire table. The database administrator decides to implement table partitioning on the OrderDate column. What is the primary benefit of this partitioning strategy?
⚠ Common exam trap
Candidates often confuse partitioning with indexing or compression, thinking it automatically solves all performance issues or reduces storage, when its core benefit is query performance via partition elimination.
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
✓
It improves query performance by enabling partition elimination, where only relevant partitions are scanned.
Table partitioning in Azure SQL Database divides a large table into smaller, manageable segments based on a partition key (here, OrderDate). The primary benefit is partition elimination: queries with filters on OrderDate can scan only the relevant partition(s) instead of the entire 500-million-row table, drastically reducing I/O and improving query performance.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
It reduces the total storage required by compressing older partitions.
Why it's wrong here
Partitioning divides a table into smaller logical segments, but it does not apply compression by itself. While you can enable row or page compression independently on individual partitions, that is a separate storage optimization, not a benefit of partitioning. The main reasons to partition are manageability, faster load/unload via partition switching, and potential query speed through partition elimination, not reducing the physical size of stored data.
- ✓
It improves query performance by enabling partition elimination, where only relevant partitions are scanned.
Why this is correct
Partition elimination is a query-processing optimization that allows Azure SQL Database to access only the partition(s) relevant to a query's predicate on the partition key, instead of scanning the entire table. When a WHERE clause includes the partitioning column and the values are sargable, the optimizer can read far fewer pages, reducing I/O and improving response time. This is particularly valuable for large fact tables in a data warehouse where reports typically filter by date ranges.
- ✗
It enforces referential integrity between partitions automatically.
Why it's wrong here
Referential integrity is enforced by database constraints, specifically primary keys and foreign keys, not by how rows are physically stored. Partitioning only controls the placement of data across filegroups or storage tiers; it has no logic to validate that a value in one table has a matching value in another. If no foreign key constraint is defined, a partitioned table allows orphaned rows just like a non-partitioned table, so this option misunderstands the role of partitioning.
- ✗
It eliminates the need for indexes on the partitioned column.
Why it's wrong here
Partitioning is not an alternative to indexing. If you remove indexes from a partitioned column, queries that filter on that column may still cause a full scan of all partitions unless the optimizer can use partition elimination, and even then, within a partition, no index means a scan without an ordered lookup. Indexes should be designed based on the workload, and while aligned indexes on the partition key can be managed with partitions, non-partition-key filters still require additional nonclustered indexes. Partitioning alone does not eliminate the need for any index.
Go deeper
Related to this question
Learn chapter
Azure SQL Services
Key term
Azure SQL Database
Azure SQL Database is a fully managed relational database-as-a-service (DBaaS) in Microsoft Azure, based on the SQL Server engine, that handles scaling, backups, patching, and high availability automatically.
Key term
Partition key
A partition key is a value used by database systems to distribute data across multiple storage partitions, enabling faster queries and efficient scaling.
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.