Question 435 of 982

Identify considerations for relational data on Azure →hardMultiple ChoiceObjective-mapped

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.”

DP-900 Practice Question: Identify considerations for relational data on Azure

This DP-900 practice question tests your understanding of identify considerations for relational data on azure. Match the stated requirement to the specific cloud service, access model, or configuration option — many options are valid in isolation but not for this scenario. A key principle to apply: table partitioning divides a large table into smaller, more manageable parts.. Once you have made your selection, read the full explanation to reinforce the concept and understand why each distractor is designed to mislead on exam day.

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?

Question 1hardmultiple choice

Full question →

A
Create a non-clustered index on the TransactionDate column.
Why wrong: 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.
B
Implement table partitioning by month on TransactionDate.
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.
C
Create a materialized view for the current month's data.
Why wrong: 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.
D
Convert the table to use a clustered columnstore index.
Why wrong: 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.

Full breakdown with real-world context →

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.

Key principle: Table partitioning divides a large table into smaller, more manageable parts.

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.
Related concept
Table partitioning divides a large table into smaller, more manageable parts.
✗
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.

Common exam traps

Common exam trap: answer the scenario, not the keyword

The trap here is that candidates often 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.

Detailed technical explanation

How to think about this question

Table partitioning in Azure SQL Database uses partition functions and schemes to map rows to filegroups based on a boundary value range (e.g., monthly). The query optimizer automatically performs partition elimination when the WHERE clause includes the partitioning column, drastically reducing I/O. For archiving, the SWITCH PARTITION command can move an entire partition to a staging table in milliseconds, as it only updates metadata pointers rather than physically moving data.

KKey Concepts to Remember

Table partitioning divides a large table into smaller, more manageable parts.
Partition elimination allows queries to scan only relevant partitions, improving performance.
Partitions can be switched out for efficient archiving or data loading.
Partitioning is ideal for tables with large amounts of historical data needing retention.

TExam Day Tips

Watch for words such as best, first, most likely and least administrative effort.
Review why wrong options are wrong, not only why the correct option is correct.

Key takeaway

Table partitioning divides a large table into smaller, more manageable parts.

Real-world example

How this comes up in practice

A media company stores terabytes of video archives that are accessed once a year for audit purposes. Moving these objects to a cold storage tier (Azure Archive, S3 Glacier, or Google Nearline) costs a fraction of hot storage. Questions like this test whether you understand storage tiers, access frequency tradeoffs, and retrieval latency requirements.

What to study next

Got this wrong? Here's your next step.

Review table partitioning divides a large table into smaller, more manageable parts., then practise related DP-900 questions on the same topic to reinforce the concept.

Related DP-900 practice-question pages

Use these pages to review the topic behind this question. This is how one missed question becomes focused revision.

Describe core data concepts practice questions

Practise DP-900 questions linked to Describe core data concepts.

Describe an analytics workload on Azure practice questions

Practise DP-900 questions linked to Describe an analytics workload on Azure.

Identify considerations for relational data on Azure practice questions

Practise DP-900 questions linked to Identify considerations for relational data on Azure.

Describe considerations for working with non-relational data on Azure practice questions

Practise DP-900 questions linked to Describe considerations for working with non-relational data on Azure.

DP-900 fundamentals practice questions

Practise DP-900 questions linked to DP-900 fundamentals.

DP-900 scenario practice questions

Practise DP-900 questions linked to DP-900 scenario.

DP-900 troubleshooting practice questions

Practise DP-900 questions linked to DP-900 troubleshooting.

Practice this exam

Start a free DP-900 practice session

Short sessions build daily habit. Longer sessions build exam-day stamina. Try a timed session to simulate real conditions.

10 questions 20 questions 30 questions 50 questions Timed 30

DP-900 practice-test guide →Study guide →Browse all practice tests

FAQ

Questions learners often ask

What does this DP-900 question test?

Identify considerations for relational data on Azure — This question tests Identify considerations for relational data on Azure — Table partitioning divides a large table into smaller, more manageable parts..

What is the correct answer to this question?

The correct answer is: 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.

What should I do if I get this DP-900 question wrong?

Review table partitioning divides a large table into smaller, more manageable parts., then practise related DP-900 questions on the same topic to reinforce the concept.

What is the key concept behind this question?

Table partitioning divides a large table into smaller, more manageable parts.

About these practice questions

Courseiva creates original exam-style practice questions with explanations and wrong-answer analysis. It does not publish real exam questions, exam dumps, or protected exam content. Learn why practice questions differ from exam dumps →

How Courseiva writes practice questions · Editorial policy

Same concept, more angles

1 more ways 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.

Keep practising

Question Discussion

Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.

Loading comments…

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.