Truncating Partitions - SQL Server 2016 TSQL Enhancements

For a speedy delete or cleaning the table and resetting some of the properties of the table, we use TRUNCATE TABLE statement. This was really useful specifically with large data table because it saves both resources and time. However this was limited to the entire table, truncating a partition was not possible with it.

SQL Server 2016 has extended this functionality by allowing us to truncate individual partitions or set of partitions. This is really useful if a partition needs to be cleaned up for refreshing data. Here is a sample code on it;

This code creates a database, table in it and inserts set of records;

-- creating database
CREATE DATABASE [Sales] ON  PRIMARY 
( NAME = N'Sales', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016_M\MSSQL\DATA\Sales.mdf' , SIZE = 3048KB , FILEGROWTH = 1024KB ), -- default FG
 FILEGROUP [CurrentFileGroup] 
( NAME = N'Sales Current', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016_M\MSSQL\DATA\Sales Current.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [HistoryFileGroup] 
( NAME = N'Sales Histoty', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016_M\MSSQL\DATA\Sales Histoty.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Sales_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016_M\MSSQL\DATA\Sales_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

USE Sales
GO

-- creating the partition
CREATE PARTITION FUNCTION pf_SalesDate (datetime)
AS RANGE RIGHT
FOR VALUES ('1/1/2003', '1/1/2004', '1/1/2005', '1/1/2006')
GO


-- creating the scheme
CREATE PARTITION SCHEME ps_SalesDate
AS PARTITION pf_SalesDate
TO (HistoryFileGroup, HistoryFileGroup, HistoryFileGroup, HistoryFileGroup, CurrentFileGroup)
GO

-- creating the partitioned table
CREATE TABLE SalesOrders
(
 ID int IDENTITY(1,1) NOT NULL,
 [Date] datetime NOT NULL,
 CustomerID int NOT NULL,
 CONSTRAINT PK_SalesOrders PRIMARY KEY (ID, [Date])
)
ON ps_SalesDate ([Date])
GO

INSERT INTO SalesOrders ([Date], CustomerID) VALUES ('01/01/2003', 1)
INSERT INTO SalesOrders ([Date], CustomerID) VALUES ('02/01/2003', 2)
INSERT INTO SalesOrders ([Date], CustomerID) VALUES ('01/01/2004', 5)
INSERT INTO SalesOrders ([Date], CustomerID) VALUES ('02/01/2004', 7)
INSERT INTO SalesOrders ([Date], CustomerID) VALUES ('05/01/2004', 15)
INSERT INTO SalesOrders ([Date], CustomerID) VALUES ('01/01/2005', 20)
INSERT INTO SalesOrders ([Date], CustomerID) VALUES ('03/01/2005', 34)
INSERT INTO SalesOrders ([Date], CustomerID) VALUES ('01/01/2006', 56)
GO 1000000 -- You can change this as you want

-- checking partitions and number of rows
SELECT p.partition_number, rows
FROM sys.partitions p
 INNER JOIN sys.objects o
  ON o.object_id = p.object_id
WHERE o.type = 'U' AND o.name = 'SalesOrders';

Let's try to clean a partition by using DELETE statement.

As you see, it takes 6 seconds for deleting. Since truncating partitions is possible now, it can be used for deleting, saving time and resources.

Truncating is possible with one partition, multiple partitions and ranges;

-- One partition
TRUNCATE TABLE dbo.SalesOrders WITH (PARTITIONS (2));

-- Multiple partitions
TRUNCATE TABLE dbo.SalesOrders WITH (PARTITIONS (2, 3, 4));

-- Range
TRUNCATE TABLE dbo.SalesOrders WITH (PARTITIONS (2 TO 4));

-- Combining range and multiple partitions
TRUNCATE TABLE dbo.SalesOrders WITH (PARTITIONS (1, 2, 4 TO 6));