Finally: TRUNCATE partition in SQL Server

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. That is what the documentation says and that is what we daily use in our scripts, jobs and SSIS packages. In SQL Server 2016,  such operation can be done on the partition level as well, giving extra flexibility and convenience. TRUNCATE has nearly no resources cost for the system during the execution,  it also require fewer locks than comparable DELETE statement.

 

Disclaimer

The highlighted results are based on pre-release version. Behavior described here is a subject for possible change. Version of SQL Server:

-- Microsoft SQL Server 2016 (CTP2.1) - 13.0.300.44 (X64)

 

Directly to an example:

Partition truncation in a SQL Server 2016 as simple as following line of code:

 --SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 324 ms.
TRUNCATE TABLE dbo.PartitionedTable WITH (PARTITIONS (1, 6 TO 10));

The command has removed partitions: 1, 6, 7, 8, 9, 10. Each one held one million rows,  therefore, 6 000 000 rows removed in a 324 ms. This is amazing result which outperform SWITCH OUT timings and seems, partition truncation is another new feature which works perfectly, even in early preview builds.

Nevertheless, TRUNCATE is a DDL command and this fact is a reason of superior performance of rows/pages release. However, it also leads to requirement of the minimum permission level on a table – ALTER.

Another necessary part of the command is the list of partition ids to remove corresponding partition. Partition Id can be obtained multiple way and as example, via $PARTITION.partition function call. Following query has been executed before and after truncation:

SELECT 
	$PARTITION.[PartitioningByRowID] (RowId) AS PartitionID
,	COUNT(*) AS [RowCount]
,	MIN(RowId) AS MinRowID
,	MAX(RowId) AS MaxRowID
FROM dbo.PartitionedTable
GROUP BY $PARTITION.[PartitioningByRowID] (RowId)
02. Truncation. Before and After
02. Truncation. Before and After

Alternative way to get partition ids and metadata is by querying querying dynamic management object – sys.dm_db_partition_stats.

 

Have to reproduce it?

Following wall of the SQL code hold all necessary parts to reproduce test on your side:

-- step 1: build partition function and partition scheme
CREATE PARTITION FUNCTION [PartitioningByRowID] (int)
AS RANGE RIGHT FOR VALUES 
(1000000, 2000000, 3000000,4000000, 5000000
, 6000000, 7000000, 8000000, 9000000,10000000);

CREATE PARTITION SCHEME [PartitionByRowID]
AS PARTITION [PartitioningByRowID]
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);

-- step 2: create partitioned table
CREATE TABLE [dbo].[PartitionedTable](
	[RowId] [INT] NOT  NULL,
	[DataFiller] [char](500) NULL,
	[DateInserted] [datetime] NOT NULL
) ON [PartitionByRowID](RowID)

GO

-- step 3: build a table
INSERT dbo.PartitionedTable
SELECT TOP 10000000 
	s3.number * 1000000  +  s2.number * 1000 + s1.number  AS RowId
,	CAST(replicate ('a',500) AS CHAR(500)) AS  DataFiller 
,	GETDATE() AS DateInserted
FROM master..spt_values s1
CROSS JOIN master..spt_values s2 
CROSS JOIN master..spt_values s3
WHERE s1.number BETWEEN 0 AND 999 AND s1.type = 'P'
AND s2.number BETWEEN 0 AND 999 AND s2.type = 'P'
AND s3.number BETWEEN 0 AND 9 AND s3.type = 'P'
ORDER BY 1

-- step 4: get number of rows per partition
SELECT 
	$PARTITION.[PartitioningByRowID] (RowId) AS PartitionID
,	COUNT(*) AS [RowCount]
,	MIN(RowId) AS MinRowID
,	MAX(RowId) AS MaxRowID
FROM dbo.PartitionedTable
GROUP BY $PARTITION.[PartitioningByRowID] (RowId)

SET STATISTICS IO, TIME ON 

-- step 5: perform partition truncation
 --SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 324 ms.
TRUNCATE TABLE dbo.PartitionedTable
WITH (PARTITIONS (1, 6 TO 10));