3.4 Configuring Table Partitioning and Data Compression

Partition Functions, Schemes, and Partitioned Tables

Table partitioning horizontally divides one logical table (or index) into multiple physical partitions based on a partitioning column - most often a date. Three objects are involved, in dependency order. A partition function defines the boundary values and how they fall: CREATE PARTITION FUNCTION pf_Monthly (datetime2) AS RANGE RIGHT FOR VALUES ('2026-01-01', '2026-02-01', '2026-03-01') creates four partitions. With RANGE RIGHT the boundary value belongs to the partition on its right (the natural choice for dates, so '2026-02-01' lands in the February partition); with RANGE LEFT it belongs to the left. Getting LEFT versus RIGHT backwards 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]) puts every partition on PRIMARY, while listing multiple filegroups spreads partitions across them. Finally you create the table or clustered index ON the scheme, naming the partitioning column: CREATE CLUSTERED INDEX cx ON dbo.Fact(SaleDate) ON ps_Monthly(SaleDate). Nonclustered indexes created on the same scheme are aligned with the table - required for partition switching.

Why partition? Manageability, not raw query speed. Partitioning enables fast data lifecycle operations (load and purge via switching), partition-level index rebuilds, partition-level compression, and placing older partitions on cheaper filegroups. The optimizer can also eliminate partitions from scans when the partitioning column is filtered (partition elimination), but partitioning alone is not a performance feature - an exam answer that partitions a table purely to speed up a query that lacks an index is wrong. SQL Server supports up to 15,000 partitions, though the practical guidance is to stay under roughly a thousand.

Switching Partitions In and Out

The SWITCH operation is the payoff: ALTER TABLE dbo.Fact SWITCH PARTITION 2 TO dbo.Fact_Staging moves partition 2's entire data set to the staging table as a metadata-only change - no rows move, so a 100-million-row purge completes in milliseconds. The requirements are strict and heavily tested:

• Source partition and target table must have identical schema: same columns, data types, and nullability; the target's indexes must be aligned (matching the source partition's indexes).
• The target must be empty. For SWITCH IN (loading), the receiving partition must be empty; for SWITCH OUT (purging), the staging table must be empty.
• For SWITCH IN, the staging table needs a CHECK constraint that guarantees its rows fall within the target partition's boundary - otherwise the engine cannot prove the data belongs there.
• Both tables must live on the same filegroup (the partition's filegroup), and compression settings must match between source and target partition.

The standard sliding-window pattern: SWITCH OUT the oldest partition to a staging table, then TRUNCATE TABLE or drop the staging table for an instant, minimally logged purge; at the other end, load new data into a staging table, add the CHECK constraint and aligned indexes, SPLIT a new boundary, and SWITCH IN.

Managing Boundaries and Filegroups

Two statements maintain the sliding window. ALTER PARTITION FUNCTION pf_Monthly SPLIT RANGE ('2026-04-01') adds a new boundary; before splitting you must tell the scheme where the new partition goes with ALTER PARTITION SCHEME ps_Monthly NEXT USED [FG_April], or the split fails. ALTER PARTITION FUNCTION pf_Monthly MERGE RANGE ('2026-01-01') removes a boundary and folds two partitions into one - used after switching out old data. Both SPLIT and MERGE are fast only when the affected partitions are empty; splitting a partition that holds data forces physical row movement and heavy logging, which is why window maintenance is scheduled while the boundary partitions are empty. To inspect where rows live, use $PARTITION, e.g. SELECT $PARTITION.pf_Monthly(SaleDate), COUNT(*) FROM dbo.Fact GROUP BY 1.

Filegroup strategy matters mainly on SQL Server VMs and Managed Instance, where you control storage: put hot partitions on fast disks and archive partitions on cheaper ones, and leverage filegroup-level backup and piecemeal restore. On Azure SQL Database everything is logically one storage layer, but ALL TO ([PRIMARY]) schemes still deliver the switching and elimination benefits.

Data Compression: Row, Page, and Columnstore

Data compression reduces storage and I/O at the cost of CPU. Three families:

• Row compression stores fixed-width types (char, int, decimal, datetime) in variable-length form and drops the metadata overhead of fixed lengths. CPU cost is small; it is broadly safe for OLTP. Savings are modest.
• Page compression is a superset: row compression plus prefix compression (common column prefixes stored once per page) plus dictionary compression (repeated values across columns replaced by pointers). Savings are larger - often 50-80% on tables with repetitive values - but CPU cost rises, especially for updates, because the engine maintains the page dictionary. Best for read-heavy or scan-heavy tables, history tables, and data marts.
• Columnstore compression applies to clustered/nonclustered columnstore indexes and typically achieves around 10x for analytical workloads. COLUMNSTORE_ARCHIVE compresses further still at the highest CPU cost, intended for partitions queried rarely (compliance archives).

Apply compression per table, index, or per partition:

ALTER INDEX cx ON dbo.Fact REBUILD PARTITION = 12 WITH (DATA_COMPRESSION = PAGE, ONLINE = ON);

Per-partition compression is the standard pattern with partitioning: hot current partition row-compressed or uncompressed, warm partitions page-compressed, cold partitions in archive columnstore. ONLINE = ON is an Enterprise-tier capability (available on all Azure SQL offerings).

Before committing, measure. EXEC sp_estimate_data_compression_savings 'dbo', 'Fact', NULL, NULL, 'PAGE' copies a representative sample into tempdb, compresses it, and returns estimated sizes with the current and requested settings - per index and partition. The tradeoff framing for the exam: compression converts an I/O-bound problem into CPU spend. It is a win when storage or scan I/O is the bottleneck and CPU headroom exists; it backfires on CPU-bound, update-heavy OLTP tables, and it cannot compress data that is already high-entropy (encrypted values, compressed media, most LOB data off-row).