When doing Partition Switching (SWITCH...TO) - why does the receiving partition need to be empty?-CodePudding

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.

CodePudding user response：

SWITCH is not a swap. The source partition is moved into the destination partition and the resultant source partition becomes empty as a result. The empty target requirement ensures existing data is not lost.

CodePudding user response：

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.