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