SQL Server 2016 News – Truncate partition

It’s very common to partition very large tables, but when you need to get rid of the data in a partition you had to do a DELETE operation or a partition switch to a new table and do a truncate of the new table. Deleting data is time and resource consuming, but finally SQL Server supports truncate of partitions in SQL Server 2016. In this sample I create a partitioned table, populate it with some data and then truncate the first partition:

SET NOCOUNT ON;

/****** Object: Table [dbo].[PartitionTest] Script Date: 2016-04-05 23:10:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO
DROP TABLE IF EXISTS dbo.PartitionTest;
GO

CREATE TABLE [dbo].[PartitionTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Namn] [varchar](50) NOT NULL,
CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [SQL2016Lab]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [pfPartitionTest](int) AS RANGE LEFT FOR VALUES (N'100', N'200')
CREATE PARTITION SCHEME [psPartitionTest] AS PARTITION [pfPartitionTest] TO ([PRIMARY], [PRIMARY], [PRIMARY])

ALTER TABLE [dbo].[PartitionTest] DROP CONSTRAINT [PK_PartitionTest]
ALTER TABLE [dbo].[PartitionTest] ADD CONSTRAINT [PK_PartitionTest] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [psPartitionTest]([ID])

COMMIT TRANSACTION
INSERT INTO dbo.PartitionTest (Namn)
SELECT top 10000 c1.name
FROM sys.columns c1
cross apply sys.columns c2
GO

TRUNCATE TABLE dbo.PartitionTest WITH(PARTITIONS(1));

SELECT * FROM dbo.PartitionTest