DP-900 Practice Question: Identify considerations for relational data on Azure
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?
⚠ Common exam trap
It's easy for candidates to confuse performance features like columnstore indexes or elastic pools with the specific need for partition elimination and data archiving, overlooking that partitioning directly addresses both the query performance and data lifecycle requirements.
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
✓
Partition the table by month and create aligned indexes.
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.
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 columnstore index on the entire table.
Why it's wrong here
A clustered columnstore index is optimized for analytical scans and large aggregations, but it does nothing to help with data lifecycle management or archival. It also adds significant write overhead for OLTP-style inserts and point updates, which are common in a financial system. This option addresses query performance only, not the need to age out old data, so it is incorrect.
- ✓
Partition the table by month and create aligned indexes.
Why this is correct
Partitioning the table by month and creating aligned indexes is correct because it enables partition elimination for queries that filter on months, directly improving performance for monthly reporting. Aligned indexes—where every index is partitioned on the same partition column—allow partition switching to be fast and atomic: you can move an entire month of historical data to an archive table in seconds without touching the rest of the table. This both simplifies archiving and keeps indexes consistent, solving both stated requirements.
- ✗
Use Azure SQL Database elastic pool for the database.
Why it's wrong here
An Azure SQL Database elastic pool is a deployment model that shares reserved resources (DTUs/vCores) among multiple databases, which is meant to manage unpredictable resource usage across many databases. It does not change how a single table is stored, queried, or archived, nor does it provide partition elimination or sliding-window data movement. Therefore, it is incorrect for this scenario.
- ✗
Implement transparent data encryption.
Why it's wrong here
Transparent data encryption (TDE) encrypts the database files, backups, and transaction logs at rest, which is a security control, not a performance or data-management feature. It does not reduce the cost of scanning a large table, nor does it offer any mechanism to move old rows to an archive. In fact, TDE can add some CPU overhead, so it would not help with monthly queries or archiving.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
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
Index
An index is a data structure that speeds up data retrieval operations on a database table or file, much like a book index helps you find topics quickly.
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 →
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.