TRUNCATE TABLE with partitions

With SQL Server 2016 we’ll see an enhancement to the TRUNCATE TABLE statement. The TRUNCATE TABLE statement now permits the truncation of specified partitions.

TRUNCATE TABLE removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with NOWHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

The new syntax looks like this:

TRUNCATE TABLE table_name
WITH (PARTITIONS (<partition_number_expression> | <range>)

Where the WITH PARTITIONS specifies the partitions to truncate or from which all rows are removed. If the table is not partitioned, the WITH PARTITIONS argument will generate an error. If the WITH PARTITIONS clause is not provided, the entire table will be truncated.

<partition_number_expression> can be specified in the following ways:

• Provide the number of a partition, for example: WITH (PARTITIONS (2))
• Provide the partition numbers for several individual partitions separated by commas, for example: WITH (PARTITIONS (1, 5))
• Provide both ranges and individual partitions, for example: WITH (PARTITIONS (2, 4, 6 TO 8))
• <range> can be specified as partition numbers separated by the word TO, for example: WITH (PARTITIONS (6 TO 8))

Demo Time
Let’s create a partitioned table, populate it with data and then try to TRUNCATE a few partitions. Here is the setup code

CREATE DATABASE Inspari_SQLServer2016
GO USE Inspari_SQLServer2016
GO CREATE PARTITION FUNCTION [PF_Seconds](Int) AS RANGE RIGHT FOR VALUES ( 5,10,15,20,25,30,35,40,45,50,55
) GO CREATE PARTITION SCHEME [PS_Seconds] AS PARTITION [PF_Seconds] ALL TO ([PRIMARY]) GO CREATE TABLE [dbo].[LogEntry]( [Id] [uniqueidentifier] NOT NULL, CreateDate datetime, colSec as Datepart(second, CreateDate) PERSISTED CONSTRAINT [PK_LogEntry_Day] PRIMARY KEY CLUSTERED (colSec, id) ON PS_Seconds(colSec) ) GO ALTER TABLE [dbo].[LogEntry] SET (LOCK_ESCALATION = AUTO) GO INSERT INTO dbo.LogEntry (id, CreateDate) VALUES (NEWID(), GETDATE()) GO 100000

After the INSERT my partitions looks like this ->

Now let’s TRUNCATE partitions 3,5 and 11

TRUNCATE TABLE LogEntry
WITH (PARTITIONS(3,5,11)) 

And the result ->

Nothing but pure Magic :)

Learn more about SQL here

Any questions?
Please reach out to info@inspari.dk or +45 70 24 56 55 if you have any questions. We are looking forward to hearing from you.