2.3 Table Partitioning and Sharding Strategy

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.

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.

Sharding Strategy and Patterns

Sharding spreads one logical data set across multiple shards — independent databases that each hold a disjoint slice of the rows. Sharding is the answer when a single database cannot meet the workload: storage exceeds the tier ceiling (for example, a General Purpose single database tops out at 4 TB, or a Managed Instance database at its tier limit), the compute required exceeds the maximum vCores on one database, or concurrent connection counts surpass one database's capacity.

Three sharding patterns map to different key distributions:

• Range sharding assigns contiguous key ranges to shards — shard 0 holds tenant IDs 1–1000, shard 1 holds 1001–2000. It suits ordered keys like dates or region codes and supports efficient range queries that stay within one shard. The risk is hot spots if activity concentrates in one range.
• Hash sharding applies a hash function to the key and routes by hash bucket, distributing high-cardinality keys evenly. It balances load well but range queries fan out across many shards, and resharding (adding a shard) requires redistributing data.
• List sharding maps explicit key values to named shards — customer A to shard 1, customer B to shard 2. It suits multi-tenant systems where a large tenant needs its own shard and small tenants share one.

Elastic Database Client Library and Shard Map Manager

For Azure SQL Database, Microsoft provides the Elastic Database Client Library (a .NET library) whose centerpiece is the shard map manager. The shard map manager maintains a global map from shard keys to shard databases and exposes two main shard map types: a list shard map (each key value maps to exactly one shard) and a range shard map (contiguous key ranges map to shards). Hash sharding is implemented by computing a hash and storing ranges against the hash output. The library handles data-dependent routing — given a query's shard key, it opens a connection to the correct shard and caches it — and multi-shard queries that fan a query out across all shards and merge the results.

The exam expects you to know the operational rules: all shards in one shard map must use the same schema for the sharded table, and each shard holds a shardlet (the unit of data movement). Moving a shardlet between shards is an online operation the library coordinates, and split-merge services exist to rebalance shards without downtime. A shard key must be present in every sharded table's primary key so rows can be routed unambiguously. Choosing the wrong shard key — a high-cardinality key that changes frequently, or a key that creates hot spots — is the classic sharding design trap.

When Partitioning vs Sharding Applies

The decision rule the exam tests:

• Partition when one database can hold all the data and the goal is fast load and purge (SWITCH), partition-level maintenance (rebuild or compress only the current partition), or partition elimination on large scans. The data stays inside one database.
• Shard when one database cannot hold the data or cannot serve the workload — storage past the tier limit, compute past max vCores, or connection counts past the database ceiling — and the data must be spread across multiple databases coordinated by a shard map.

A frequent trap: an answer proposes sharding a 2 TB table that fits comfortably on a General Purpose database to solve a purge performance problem. The right answer is partitioning with a sliding-window SWITCH, because the database has not exceeded its scale limit and sharding adds routing complexity the scenario does not need. Conversely, when a scenario explicitly states the single-database storage or compute ceiling has been reached, partitioning alone cannot help because partitions live inside one database — sharding is the only option that crosses database boundaries.