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 speeds up searches for specific date ranges but does not help with large full-table scans for monthly aggregations, nor does it simplify archiving.
- ✓
Implement table partitioning by month on TransactionDate.
Why this is correct
Partitioning enables partition elimination for queries filtering on TransactionDate, reducing scan size. Old partitions can be switched out for easy archiving without impacting the live table.
- ✗
Create a materialized view for the current month's data.
Why it's wrong here
A materialized view can pre-aggregate data for faster queries, but it does not reduce the scan on the base table and does not provide an easy archiving mechanism for old data.
- ✗
Convert the table to use a clustered columnstore index.
Why it's wrong here
A columnstore index improves performance for analytic scans but still requires scanning all partitions or the entire table. It does not simplify archiving of old data.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
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 820 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.