Using Table Partitioning in Azure SQL Database for Query Performance and Data Archiving
A company uses Azure SQL Database for a financial system. The Transactions table contains millions of rows with a TransactionDate column. Queries frequently aggregate sales totals for the current month, but historical data must be retained for 7 years. Currently, queries scan the entire table, causing performance issues. The company also wants to simplify archiving of old data. Which design should they implement?
Quick Answer
The answer is to implement table partitioning by month on TransactionDate. This design directly addresses both query performance and data archiving by splitting the large Transactions table into smaller, manageable segments based on the date column. When queries aggregate sales totals for the current month, Azure SQL Database performs partition elimination, scanning only the relevant partition instead of the entire table, which drastically reduces I/O and speeds up execution. For archiving, partitioning enables swift partition switching to move historical data older than seven years to an archive table without complex ETL processes. On the DP-900 exam, this scenario tests your understanding of how partitioning supports both performance tuning and lifecycle management in Azure SQL Database. A common trap is confusing partitioning with indexing—remember that partitioning physically separates data, while indexing organizes it within partitions. A helpful memory tip: “Partition by date to eliminate and archive.”
⚠ Common exam trap
It's easy for candidates to choose a non-clustered index (Option A) thinking it will speed up range queries, but they overlook that partitioning is specifically designed for both performance on large tables and simplified data lifecycle management, which the question explicitly requires.
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
✓
Implement table partitioning by month on TransactionDate.
Table partitioning by month on TransactionDate allows Azure SQL Database to efficiently manage and query large tables by splitting data into manageable segments. Queries that filter on TransactionDate for the current month will only scan the relevant partition(s), eliminating full table scans. Additionally, partitioning simplifies archiving by enabling swift partition switching to move old data to archive tables without complex ETL processes.
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 the TransactionDate column.
Why it's wrong here
A non-clustered index on TransactionDate speeds date-range seeks but leaves all seven years in one table, so archiving still requires row-by-row deletes. Such indexes suit selective point or range lookups on modest tables, not simplifying bulk removal of old data.
- ✓
Implement table partitioning by month on TransactionDate.
Why this is correct
Partitioning splits the Transactions table into monthly partitions on TransactionDate, enabling partition elimination so current-month aggregates scan only the relevant partition rather than millions of rows. Switching partitions also simplifies archiving by moving old partitions out efficiently.
- ✗
Create a materialized view for the current month's data.
Why it's wrong here
A materialised view precomputes current-month aggregates but the underlying table still holds every historical row, so archiving remains manual and storage grows. Materialised views suit repeatedly executing expensive joins or aggregations, not partitioning data for retention and easy removal.
- ✗
Convert the table to use a clustered columnstore index.
Why it's wrong here
A clustered columnstore index speeds analytical scans but stores all millions of rows in one table, so current-month aggregates still read historical data and archiving stays manual. Columnstore suits large fact tables queried wholly for analytics, not date-bounded retention with cheap partition removal.
Go deeper
Related to this question
Learn chapter
Cosmos DB Partitioning Strategies
Key term
Column
A column is a vertical set of values in a database table that stores one specific type of attribute for every row.
Key term
Data
Data is raw, unprocessed information, like numbers, words, or measurements, that can be stored, processed, and analyzed by computers.
About these practice questions
One of 851 original DP-900 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on DP-900
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. A company uses Azure SQL Database for a financial system. The Transactions table contains millions of rows. Queries frequently aggregate data for the current month, but also need to retain historical data for 7 years. The company wants to improve query performance for the monthly aggregations and simplify data archiving. Which design should they implement?
medium- A.Create a clustered columnstore index on the entire table.
- ✓ B.Partition the table by month and create aligned indexes.
- C.Use Azure SQL Database elastic pool for the database.
- D.Implement transparent data encryption.
Why B: Partitioning the Transactions table by month allows SQL Server to perform partition elimination during queries that aggregate data for the current month, scanning only the relevant partition(s) instead of the entire table. Aligned indexes ensure that index structures follow the same partition scheme, maintaining efficiency for both queries and maintenance. This design also simplifies data archiving by enabling fast partition switching to move older months out of the table without costly delete operations.
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.