Databases, DP-800, Microsoft Certification, Uncategorized

Design and implement partitioning for tables and indexes (DP-800 Exam Prep)

 
This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Design and implement database objects
      --> Design and implement partitioning for tables and indexes

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

As databases grow from thousands to millions or even billions of rows, managing and querying data efficiently becomes increasingly challenging. Large tables can lead to longer query execution times, larger maintenance windows, slower backups, and increased index fragmentation. SQL Server and Azure SQL provide table and index partitioning to help address these challenges.

Partitioning divides a large table or index into smaller, more manageable pieces called partitions. Although users and applications continue to view the data as a single table, SQL Server stores and manages the data in separate partitions based on a defined partitioning strategy.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

  • What partitioning is
  • Benefits and limitations of partitioning
  • Partition functions
  • Partition schemes
  • Partition elimination
  • Partition switching
  • Partitioned indexes
  • Maintenance strategies
  • Best practices

Partitioning is especially valuable in AI-enabled database solutions that store large volumes of historical, telemetry, or transactional data.

What Is Table Partitioning?

Table partitioning divides one logical table into multiple physical partitions.

Applications continue to query the table normally:

SELECT *
FROM Sales;

Internally, SQL Server stores the data across multiple partitions.

Example:

Sales Table
│
├── Partition 1 (2022)
├── Partition 2 (2023)
├── Partition 3 (2024)
└── Partition 4 (2025)

Each partition contains only a subset of the rows.

Why Partition Tables?

Partitioning improves the manageability of very large tables.

Benefits include:

  • Faster maintenance
  • Easier archival
  • Improved query performance through partition elimination
  • Faster index maintenance
  • Improved data loading
  • Simplified backup strategies
  • Better scalability

It is important to understand that partitioning alone does not automatically improve every query. Benefits are greatest when queries filter on the partitioning column.

Common Partitioning Scenarios

Partitioning is commonly used for:

  • Sales history
  • Financial transactions
  • IoT telemetry
  • Sensor data
  • Event logs
  • AI inference logs
  • Audit records
  • Web clickstream data
  • Time-series databases

Most implementations partition by date.

Horizontal vs. Vertical Partitioning

Horizontal Partitioning

Rows are divided across partitions.

Example:

Sales
-----------------------
2022 rows
2023 rows
2024 rows
2025 rows

SQL Server table partitioning is horizontal partitioning.

Vertical Partitioning

Columns are divided into separate tables.

Example:

Customer Table

  • CustomerID
  • Name
  • City

CustomerDetails Table

  • CustomerID
  • Biography
  • Photo

Vertical partitioning is a database design technique, not SQL Server table partitioning.

Partition Functions

A partition function determines how rows are assigned to partitions.

It defines boundary values.

Example:

CREATE PARTITION FUNCTION pfSalesDate
(DATE)
AS RANGE RIGHT
FOR VALUES
(
('2023-01-01'),
('2024-01-01'),
('2025-01-01')
);

The partition function divides data according to the specified boundary values.

RANGE LEFT vs. RANGE RIGHT

Partition functions support two boundary options.

RANGE LEFT

Boundary value belongs to the partition on the left.

Example:

Boundary:

100

Value 100 belongs to:

Partition 1

RANGE RIGHT

Boundary value belongs to the partition on the right.

Example:

Boundary:

100

Value 100 belongs to:

Partition 2

Candidates should understand the difference because it frequently appears in certification exams.

Partition Schemes

A partition function determines how rows are divided.

A partition scheme determines where those partitions are stored.

Example:

CREATE PARTITION SCHEME psSales
AS PARTITION pfSalesDate
ALL TO ([PRIMARY]);

Alternatively, different partitions may reside on different filegroups.

Example:

2022 → FG_2022
2023 → FG_2023
2024 → FG_2024
2025 → FG_2025

Creating a Partitioned Table

Example:

CREATE TABLE Sales
(
    SaleID INT,
    SaleDate DATE,
    Amount MONEY
)
ON psSales(SaleDate);

Rows are automatically placed into the appropriate partition based on the SaleDate value.

Filegroups

Partitions can be stored in different filegroups.

Benefits include:

  • Independent backup
  • Independent restore
  • Better storage management
  • Distribution across storage devices

Although many Azure SQL Database deployments use the PRIMARY filegroup, understanding filegroups remains important for SQL Server and the DP-800 exam.

Partition Elimination

One of the biggest advantages of partitioning is partition elimination.

Instead of scanning every partition, SQL Server reads only the partitions needed for the query.

Example:

SELECT *
FROM Sales
WHERE SaleDate
BETWEEN '2025-01-01'
AND '2025-01-31';

SQL Server may read only the partition containing January 2025 data.

Benefits include:

  • Reduced I/O
  • Faster execution
  • Lower CPU usage

Partition elimination works best when predicates reference the partitioning column.

Partitioned Indexes

Indexes can also be partitioned.

Types include:

  • Clustered indexes
  • Nonclustered indexes
  • Columnstore indexes

A partitioned index aligns with the table partitions.

Aligned Indexes

An aligned index uses:

  • The same partition function
  • The same partition scheme

Benefits:

  • Easier maintenance
  • Faster partition switching
  • Simplified index rebuilds

Microsoft generally recommends aligned indexes whenever possible.

Non-Aligned Indexes

A non-aligned index uses different partitioning than the underlying table or is not partitioned at all.

Advantages:

  • Flexibility

Disadvantages:

  • More complex maintenance
  • Cannot participate in some partition operations
  • May reduce the benefits of partition switching

Partition Switching

Partition switching is one of SQL Server’s most powerful maintenance features.

Instead of copying millions of rows, SQL Server simply changes metadata.

Example:

Current Table
│
├── Partition 2024
├── Partition 2025
└── Partition 2026
↓
Switch 2024
↓
Archive Table

The operation completes very quickly because no data movement occurs.

Benefits of Partition Switching

Typical uses include:

  • Archiving old data
  • Loading new data
  • ETL processing
  • Data warehouse maintenance
  • Rolling window scenarios

Large tables can be maintained with minimal downtime.

Sliding Window Technique

Many databases maintain a rolling time window.

Example:

Keep:
2023
2024
2025
Remove:
2022
Add:
2026

Partition switching makes this process extremely efficient.

Index Maintenance

Large indexes can be rebuilt one partition at a time.

Example:

ALTER INDEX IX_Sales
ON Sales
REBUILD PARTITION = 4;

Benefits:

  • Shorter maintenance windows
  • Less locking
  • Reduced resource consumption

Statistics

Each partition maintains its own data distribution statistics.

Accurate statistics help the SQL Server Query Optimizer generate efficient execution plans.

Regular statistics updates remain important for partitioned tables.

Choosing a Partition Key

The partition key should:

  • Be commonly filtered
  • Divide data evenly
  • Support partition elimination
  • Match maintenance requirements

Good candidates include:

  • TransactionDate
  • OrderDate
  • EventDate
  • CustomerRegion
  • FiscalYear

Date columns are the most common partition keys.

When Not to Partition

Partitioning is not appropriate for every table.

Avoid partitioning when:

  • Tables are small.
  • Queries rarely filter on the partition key.
  • Maintenance requirements are minimal.
  • Administrative complexity outweighs the benefits.

Partitioning introduces additional design and maintenance considerations.

AI-Enabled Database Scenarios

Partitioning is valuable in AI-enabled solutions because AI systems often generate large volumes of data.

Examples include:

  • Prompt history
  • Chat logs
  • Model inference records
  • Telemetry
  • IoT streams
  • Sensor data
  • Feature store history
  • Training datasets
  • Experiment tracking

Partitioning enables efficient archival, querying, and maintenance of these growing datasets.

Best Practices

  • Partition only large tables that benefit from improved manageability or query performance.
  • Choose a partition key that aligns with common filtering patterns.
  • Use aligned indexes whenever practical.
  • Partition by date for most time-series workloads.
  • Use partition elimination to reduce unnecessary I/O.
  • Use partition switching for fast archival and data loading.
  • Monitor partition sizes to avoid skewed data distribution.
  • Keep statistics updated on partitioned tables.
  • Test execution plans to confirm partition elimination is occurring.

Common Exam Tips

For the DP-800 exam, remember these key points:

  • A partition function defines how rows are divided into partitions.
  • A partition scheme maps partitions to filegroups.
  • Partition elimination allows SQL Server to read only the necessary partitions when queries filter on the partition key.
  • Partition switching is a metadata operation and does not physically copy data.
  • Aligned indexes use the same partition function and partition scheme as the underlying table.
  • RANGE LEFT and RANGE RIGHT determine which partition contains the boundary value.
  • Partitioning improves manageability and can improve query performance, but it does not automatically make every query faster.

Practice Exam Questions

Question 1

A company stores ten years of sales data and frequently queries only the current month’s transactions. Which SQL Server feature can help reduce the amount of data scanned by these queries?

A. Database mirroring

B. Table partitioning

C. Row-level security

D. Dynamic data masking

Answer: B

Explanation: Table partitioning, combined with partition elimination, enables SQL Server to access only the relevant partition when queries filter on the partitioning column, reducing I/O and improving performance.

Question 2

What is the primary purpose of a partition function?

A. To define the physical storage location of partitions

B. To create indexes for each partition

C. To determine how rows are assigned to partitions based on boundary values

D. To rebuild fragmented indexes

Answer: C

Explanation: A partition function defines the partition boundaries and determines which partition stores each row.

Question 3

Which SQL Server object maps partitions to one or more filegroups?

A. Partition scheme

B. Partition function

C. File stream

D. Sequence

Answer: A

Explanation: A partition scheme associates the partitions defined by a partition function with specific filegroups.

Question 4

A query filters on the partitioning column of a partitioned table. Which optimization allows SQL Server to read only the required partitions?

A. Predicate pushdown

B. Partition elimination

C. Batch mode execution

D. Adaptive joins

Answer: B

Explanation: Partition elimination enables SQL Server to skip partitions that cannot contain qualifying rows, reducing I/O and improving performance.

Question 5

Which statement accurately describes partition switching?

A. It copies data row by row between tables.

B. It compresses partitions before moving them.

C. It moves an entire partition using a metadata operation without copying the data.

D. It permanently merges two partitions into one.

Answer: C

Explanation: Partition switching is a metadata-only operation that quickly transfers a partition between compatible tables without physically moving the data.

Question 6

Which partitioning strategy is most commonly used for large transactional and historical databases?

A. Partitioning by customer name

B. Partitioning by transaction date

C. Partitioning by product description

D. Partitioning by postal code

Answer: B

Explanation: Date-based partitioning is common because it supports efficient querying, maintenance, archival, and sliding-window scenarios.

Question 7

Which statement about aligned indexes is correct?

A. They always use a different partition scheme than the table.

B. They cannot be rebuilt independently.

C. They use the same partition function and partition scheme as the underlying table.

D. They eliminate the need for clustered indexes.

Answer: C

Explanation: An aligned index shares the same partition function and partition scheme as its table, simplifying maintenance and enabling features such as partition switching.

Question 8

What is the primary benefit of rebuilding an index one partition at a time?

A. It automatically repartitions the table.

B. It reduces maintenance impact by limiting the work to the affected partition.

C. It converts nonclustered indexes into clustered indexes.

D. It eliminates the need to update statistics.

Answer: B

Explanation: Rebuilding only the affected partition reduces resource usage, shortens maintenance windows, and minimizes locking compared to rebuilding the entire index.

Question 9

Which statement best describes RANGE RIGHT in a partition function?

A. Boundary values belong to the partition on the left.

B. Boundary values are ignored.

C. Boundary values are stored in every partition.

D. Boundary values belong to the partition on the right.

Answer: D

Explanation: With RANGE RIGHT, rows containing the boundary value are placed into the partition to the right of the boundary.

Question 10

A company maintains five years of historical telemetry data and archives the oldest year every January while adding a new year’s partition. Which partitioning technique best supports this maintenance strategy?

A. Computed columns

B. Filtered indexes

C. Sliding window partitioning using partition switching

D. Indexed views

Answer: C

Explanation: A sliding-window strategy combined with partition switching enables administrators to efficiently archive old partitions and add new ones with minimal downtime because the operation is metadata-based.

Go to the DP-800 Exam Prep Hub main page

Leave a comment