SQL Server 2016 News – Truncate partition

Oopps! Upgrade your browser pretty please.Oopps! Upgrade your browser pretty please.

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:

[code lang=”sql”]

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

[/code]