4 min læsning

TRUNCATE TABLE with partitions

Featured Image

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:

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

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



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.