'When doing Partition Switching (SWITCH...TO) - why does the receiving partition need to be empty?
I'm monkeying around with SWITCH...TO, as a 'good practice' way to do a TRUNCATE & rebuild of a table.
INSERT INTO stg.MyTable SELECT * FROM CTE_Final
SET XACT_ABORT, NOCOUNT ON
BEGIN TRANSACTION;
*TRUNCATE TABLE dbo.MyTable*
ALTER TABLE stg.MyTable
SWITCH TO dbo.MyTable
COMMIT TRANSACTION;
Per Microsoft, when doing partition switching, the destination partition must be empty.
SQL Server 2008R2 - Transferring Data Efficiently by Using Partition Switching
Why?
I'm conceptualizing this as what originally pointed to stg.MyTable, now points to dbo.MyTable, and vice versa. I'm thinking that's straight-up wrong, as I don't see why the 'switch' would require the 'destination' table to be empty. Or even why one table in particular is called the destination when they're both switching.
Solution 1:[1]
SWITCH is not a swap. The source partition is moved into the destination partition and the source partition becomes empty as a result. The empty target requirement ensures existing data is not inadvertently lost.
Solution 2:[2]
No particular reason; it happened to be designed that way. The paradigm case is rolling window partitioning, which only requires removing a partition from a table, not replacing it with another partition.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 |