2.3 Table Partitioning and Sharding Strategy

DP-300: Administering Microsoft Azure SQL Solutions Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources

2 Planning and Deploying Azure SQL Solutions

2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching

3 Configuring Resources for Scale and Performance

3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression

4 Planning and Implementing a Migration Strategy

4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting

5 Configuring Database Authentication and Authorization

5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management

6 Implementing Security for Data at Rest and in Transit

6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints

7 Implementing Compliance Controls for Sensitive Data

7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL

8 Monitoring Resource Activity and Performance

8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events

9 Monitoring and Optimizing Query Performance

9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights

10 Configuring Database Solutions for Optimal Performance

10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing

11 Creating and Managing SQL Server Agent Jobs

11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates

12 Automating Deployments and Database Tasks in Azure

12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks

13 Planning an HA/DR Strategy and Backup and Restore

13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations

14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups

14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups

15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting

15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions

2.3 Table Partitioning and Sharding Strategy

Key Takeaways Explain This Topic AI

Table partitioning horizontally slices one logical table across a partition function and scheme, enabling partition elimination and metadata-only SWITCH operations; it is a manageability feature, not a query-speed feature by itself.

Sharding distributes data across multiple physical databases (shards) when a single database's compute, storage, or connection limits are insufficient; the Elastic Database Client Library provides the shard map manager for routing.

Pick range sharding for contiguous keys like dates or regions, hash sharding for even distribution of high-cardinality keys, and list sharding when specific key values map to dedicated shards.

Azure SQL Database supports up to 15,000 partitions but all partitions live on the PRIMARY filegroup since user-managed filegroups are not exposed; Managed Instance and SQL Server on VMs support multiple filegroups for partition placement.

Choose partitioning when one database can hold the data but needs fast load and purge; choose sharding when one database cannot hold the data or the workload exceeds single-database resource limits.

Last updated: August 2026

Why Partitioning and Sharding Both Exist

Quick Answer: Use table partitioning when the data fits in one database but you need fast load and purge, partition-level maintenance, and partition elimination on scans. Use sharding when the data or the workload exceeds what one database can hold, forcing you to spread rows across multiple databases that cooperate through a shard map.

The DP-300 exam draws a hard line between the two because they solve different scale problems at different layers. Partitioning is an in-database, storage-engine feature: one table, one database, many partitions. Sharding is an application-and-infrastructure pattern: many databases, each holding a slice of the logical table, coordinated by a shard map. A question that describes a 3 TB fact table with monthly purge requirements points to partitioning; a question that describes a 80 TB tenant table exceeding one database's storage ceiling points to sharding.

Partition Function, Scheme, and Filegroups

Partitioning uses three objects in dependency order. A partition function defines the boundary values and their side: CREATE PARTITION FUNCTION pf_Monthly (datetime2)

AS RANGE RIGHT FOR VALUES ('2026-01-01', '2026-02-01', '2026-03-01');

With RANGE RIGHT the boundary value lands in the partition on its right (the natural choice for dates, so 2026-02-01 belongs to the February partition). With RANGE LEFT it lands in the left partition. Reversing LEFT and RIGHT is a classic exam trap. A partition scheme maps the function's partitions onto filegroups : CREATE PARTITION SCHEME ps_Monthly

AS PARTITION pf_Monthly ALL TO ([PRIMARY]);

Finally the table or clustered index is created ON the scheme , naming the partitioning column. Nonclustered indexes created on the same scheme are aligned with the table, which is a prerequisite for partition switching.

• Concept Purpose Exam cue
• Partition function Defines boundary values and LEFT/RIGHT RANGE RIGHT is the natural pick for dates
• Partition scheme Maps partitions to filegroups ALL TO ([PRIMARY]) when only one filegroup
• Aligned index Created on the same scheme as the table Required for SWITCH PARTITION

Partition elimination Optimizer skips partitions not matching the filter Needs the partitioning column in the predicate

Partition Elimination and the Manageability Payoff

Partition elimination is the optimizer's ability to skip whole partitions when the query predicate filters on the partitioning column. Scan a monthly-partitioned table with WHERE SaleDate BETWEEN '2026-02-01' AND '2026-02-28' and only the February partition is touched. Elimination is free but it is not a reason to partition a table that does not also need the manageability benefits — a well-designed nonclustered index serves a point lookup better than partitioning ever will.

The real payoff is metadata-only operations . ALTER TABLE dbo.Fact SWITCH PARTITION 1 TO dbo.Fact_Archive moves a whole partition's data with no physical row movement, so a 100-million-row purge completes in milliseconds. The sliding-window pattern — SWITCH OUT the oldest partition to a staging table, TRUNCATE the staging table, SPLIT a new boundary at the leading edge — is the canonical data lifecycle pattern the exam tests. SWITCH requires the source and target to be structurally identical (same columns, aligned indexes, matching compression) and the target to be empty.

Azure SQL Database vs Managed Instance Partition Differences

The platform changes where partitions can live. Azure SQL Database exposes only the PRIMARY filegroup, so every partition function and scheme maps to PRIMARY — you keep the SWITCH and elimination benefits but lose filegroup-level storage tiering and piecemeal restore. SQL Server supports up to 15,000 partitions per table; Azure SQL Database inherits the same engine limit. SQL Managed Instance supports user-defined filegroups , so you can place hot current partitions on faster storage and old partitions on cheaper storage, and leverage filegroup-level backup and piecemeal restore. SQL Server on Azure VMs gives full filegroup control plus the ability to place filegroups on different physical disks.

Horizontal vs Vertical Partitioning

Two axis distinctions appear on the exam. Horizontal partitioning splits rows by a key (the partitioning column) — every partition has the same columns but a disjoint set of rows; this is what table partitioning does. Vertical partitioning splits columns — some columns live in one table, the rest in another, joined by a shared key; this is usually done with normalized table design or sparse columns, not the partition function/scheme machinery. When a question says partitioning, it means horizontal unless it explicitly describes splitting wide tables by column groups.