https://pragmaticworks.com/blog/table-partitioning-in-sql-server-partition-switching
Partition switching moves entire partitions between tables almost instantly. It is extremely fast because it is a metadata-only operation that updates the location of the data, no data is physically moved. New data can be loaded to separate tables and then switched in, old data can be switched out to separate tables and then archived or purged. All data preparation and manipulation can be done in separate tables without affecting the partitioned table.
Partition Switching Requirements
There are always two tables involved in partition switching. Data is switched from a source table to a target table. The target table (or target partition) must always be empty.
(The first time I heard about partition switching, I thought it meant “partition swapping“. I thought it was possible to swaptwo partitions that both contained data. This is currently not possible, but I hope it will change in a future SQL Server version.)
Partition switching is easy – as long as the source and target tables meet all the requirements 🙂 There are many requirements, but the most important to remember are:
- The source and target tables (or partitions) must have identical columns, indexes and use the same partition column
- The source and target tables (or partitions) must exist on the same filegroup
- The target table (or partition) must be empty
If all the requirements are not met, SQL Server is happy to tell you exactly what went wrong and provides detailed and informative error messages.
ALTER TABLE tbTable01 SWITCH PARTITION 56 TO tbTable01ARCH PARTITION 56
Comments