Printed 2017/08/24 03:10AM

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

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.