SQL Partition SWITCH Operation#

With the power of the table partitioning SWITCH operation you can swap table metadata pointers instantly.

This can be used on any modern edition of sql server, even if table partioning is not supported. This is because under the hood every table is a single partition. With this you can quickly move data to an empty table and with some “shell games” instantly swap back to the old table.

I have used this many times to remove billions of rows by selecting out only the few hundred thousand that I wanted to keep into a new table and swapping table partition pointers. The swap is instant and there is little to no downtime.

Let’s walk through an example of the process.

1
-- create test set of tables
2
CREATE TABLE dbo.TEST1(id INT)
3
CREATE TABLE dbo.TEST2(id INT)
4

5
-- populate test1 with 10000 rows of data
6
DECLARE @i INT = 0
7
WHILE @i < 10000
8
BEGIN
9
    INSERT INTO dbo.TEST1(id)
10
    VALUES(@i)
11
    SELECT @i += 1
12
END
13

14
-- view current state of things
15
SELECT * FROM dbo.TEST1
16
SELECT * FROM dbo.TEST2
17

18
-- switch data to second table
19
ALTER TABLE dbo.TEST1
20
SWITCH TO dbo.TEST2
21

22
-- view current state of things
23
SELECT * FROM dbo.TEST1
24
SELECT * FROM dbo.TEST2

Now that we have an idea of how this works, you can imagine that you have a table with billions of rows that you want to delete. DELETEs have to acquire a lock on the table at some level potentially preventing others from interacting with it. SELECTs however are much quicker and can be done in parallel without affecting others.

Below, we select out just the handful of rows that we want, truncate the original table, then swap the table pointers.

1
-- =========================
2
-- TRUNCATE TABLE SHELL GAME
3
-- =========================
4
-- move out set of data i want to keep
5
SELECT TOP 1000 * INTO dbo.TEMP_SHELL FROM dbo.TEST2
6

7
-- truncate orig table
8
TRUNCATE TABLE dbo.TEST2
9

10
-- move data metadata from shell to orig table
11
ALTER TABLE dbo.TEMP_SHELL
12
SWITCH TO dbo.TEST2
13

14
-- view current state of things
15
SELECT * FROM dbo.TEMP_SHELL
16
SELECT * FROM dbo.TEST2
17

18
-- remove shell table
19
DROP TABLE dbo.TEMP_SHELL
20

21
-- view current state of things
22
SELECT * FROM dbo.TEST1
23
SELECT * FROM dbo.TEST2
24

25
-- cleanup
26
DROP TABLE dbo.TEST1
27
DROP TABLE dbo.TEST2

This process has saved me countless hours of data processing and downtime.

I have used it in ETLs, ad-hoc table cleanups, as well as to create a simple data archiving process that moves old data by quarter into another table for historical reporting keeping the primary table lean.

This may not be such a concern now that Microsoft has opened up the table partitioning feature to all editions of SQL Server, but still useful for situations where the complexity of managing table partitioning outweighs the benifits.

~ SK