Table swapping switcheroo (PARTITION SWITCH)

I love finding an obscure, rarely talked about feature in SQL Server, and today that is ALTER TABLE…SWITCH PARTITION

I was working with a query that was doing several calculations and would take 10- 15 seconds to complete. It was being executed 20-30 times a minute by different users and was causing problems… BLOCKING…eating up CPU…just being a nuisance. So today I needed to build a process to persist that computed data into a table to help make some queries more efficient. The data would need to be refreshed at a certain interval, but the table couldn’t be empty. I decided to give ALTER TABLE…SWITCH PARTITION a shot.

You can download the code HERE

USE AdventureWorks2012
GO

/*create our table structures. they must match, including all indexes*/
IF EXISTS (
SELECT 1
FROM sys.tables
WHERE NAME = 'table1'
)
BEGIN
DROP TABLE dbo.table1
END

IF EXISTS (
SELECT 1
FROM sys.tables
WHERE NAME = 'table2'
)
BEGIN
DROP TABLE dbo.table2
END

CREATE TABLE dbo.table1 (
EmployeeID INT PRIMARY KEY CLUSTERED
,OrderCount INT
,AvgTotalDue DECIMAL(19, 5)
)

CREATE TABLE dbo.table2 (
EmployeeID INT PRIMARY KEY CLUSTERED
,OrderCount INT
,AvgTotalDue DECIMAL(19, 5)
)

/*put some data into the table1*/
INSERT INTO dbo.table1 (
EmployeeID
,OrderCount
,AvgTotalDue
)
SELECT o.EmployeeID
,COUNT(0) [Count]
,AVG(o.TotalDue) [AvgTotalDue]
FROM Purchasing.PurchaseOrderHeader O
GROUP BY o.EmployeeID

/*check our counts*/
SELECT COUNT(0) [table1]
FROM dbo.table1

SELECT COUNT(0) [table2]
FROM dbo.table2

/*clear table2. we want to make
sure this is empty because the data is going
to get moved into table1 */

TRUNCATE TABLE table2

/*stage table2...we are only going to grab top 5 rows */
INSERT INTO dbo.table2 (
EmployeeID
,OrderCount
,AvgTotalDue
)
SELECT TOP (5) o.EmployeeID
,COUNT(0) [Count]
,AVG(o.TotalDue) [AvgTotalDue]
FROM Purchasing.PurchaseOrderHeader O
GROUP BY o.EmployeeID

SELECT COUNT(0) [table1]
FROM dbo.table1

SELECT COUNT(0) [table2]
FROM dbo.table2

/*dclemens 2018-02-23 */
BEGIN TRAN

--The switch table must be empty
TRUNCATE TABLE dbo.table1

--switcheroo
ALTER TABLE dbo.table2 SWITCH PARTITION 1 TO dbo.table1 PARTITION 1

COMMIT TRAN

SELECT COUNT(0) [table1]
FROM dbo.table1

SELECT COUNT(0) [table2]
FROM dbo.table2