Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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

Comments

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

Loading comments...