SQL Server table partition switching

If you are using partitions in SQL Server, be aware that “partition switching” is a great feature to quickly truncate partitions or to load data in parallel.

To truncate a partition:

  1. Create a staging table with the schema structure matching the source partitioned table
  2. If they are in different filegroups, then move the staging table to the same filegroup as the filegroup of the partition of the source partitioned table. [“ALTER TABLE TABLE_NAME DROP CONSTRAINT with MOVE TO file-group” command is used for moving a table from one filegroup to another.  The constraint should be clustered]
  3. If there are indexes (clustered and nonclustered) on the source partitioned table the same indexes should be created on the staging table.  This is a requirement for SWITCH PARTITION statement
  4. Use the ALTER TABLE SWITCH PARTITION statement, specifying you are switching from the source partitioned table to the staging table.  This moves the data of the source partitioned table to the staging table, effectively truncating the source partitioned table.  This happens immediately as the data is not actually being moved, but rather the pointers to the data are changed (a metadata-only operation)
  5. Drop the staging table

You can also use partition switching to load data in parallel: Create staging partitions with different ranges that are identical in structure to the source partitions, load those staging partitions in parallel, then use the ALTER TABLE SWITCH PARTITION statement to move those staging partitions to the source table.

More info:

Transferring Data Efficiently by Using Partition Switchingtruncate partition of partitioned table

How To Decide if You Should Use Table Partitioning

Truncate Table Partition command in SQL Server

Partitioned Table and Index Strategies Using SQL Server 2008

Implementation of partition switching within SSIS

About James Serra

James is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.
This entry was posted in SQL Server, SQLServerPedia Syndication. Bookmark the permalink.