SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. 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 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...