Study guide
3.4 Configuring Table Partitioning and Data Compression
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
3.4 Configuring Table Partitioning and Data Compression
Key Takeaways Explain This Topic AI
A partition function defines boundary values (RANGE LEFT puts the boundary value in the left partition); a partition scheme maps those partitions to filegroups, and the table or index is created ON the scheme
ALTER TABLE ... SWITCH PARTITION is a metadata-only operation that moves a whole partition instantly - the target table must be empty and structurally identical, including aligned indexes
ALTER PARTITION FUNCTION SPLIT RANGE adds a boundary and MERGE RANGE removes one; always mark the destination filegroup with ALTER PARTITION SCHEME NEXT USED before splitting
Row compression stores fixed-width data in variable-length form; page compression adds prefix and dictionary compression on top; columnstore (and the more aggressive COLUMNSTORE_ARCHIVE) suits analytical workloads
sp_estimate_data_compression_savings samples the object in tempdb and projects savings for row or page compression per partition - run it before committing to a compression strategy
Last updated: August 2026
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.