SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Update to TRUNCATE TABLE in SQL 2016 (partition support)

Continuing my obsession with partitioning I thought I’d write this quick post about a cool change in SQL Server 2016.

This change allows you to truncate an individual partition, instead of having to switch that partition out to another table and then truncate it. Full details can be found here: – https://msdn.microsoft.com/en-us/library/ms177570.aspx

Here’s a demo, initial setup to create a database, partition function & scheme and then a table: –

CREATE DATABASE [PartitioningDemo]
 ON PRIMARY
(NAME = N'PartitionDemo', FILENAME = N'C:\SQLServer\SQLData\PartitionDemo.mdf', SIZE = 51200KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB), 
 FILEGROUP [DATA] 
(NAME = N'DATA', FILENAME = N'C:\SQLServer\SQLData\DATA.ndf', SIZE = 51200KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
 FILEGROUP [ARCHIVE] 
(NAME = N'ARCHIVE', FILENAME = N'C:\SQLServer\SQLData\ARCHIVE.NDF', SIZE = 51200KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
(NAME = N'PartitionDemo_log', FILENAME = N'C:\SQLServer\SQLLog\PartitionDemo_log.ldf', SIZE = 20480KB, MAXSIZE = 2048GB, FILEGROWTH = 512KB)
GO

USE [PartitioningDemo];
GO

CREATE PARTITION FUNCTION PF_PartitionedTable(DATE)
       AS RANGE RIGHT
    FOR VALUES ('2014-01-01','2015-01-01','2016-01-01');

 
CREATE PARTITION SCHEME PS_PartitionedTable
    AS PARTITION PF_PartitionedTable
TO ([ARCHIVE],[ARCHIVE],[DATA],[DATA]);


CREATE TABLE dbo.PartitionedTable
(PKID INT IDENTITY(1,1),
 ColA VARCHAR(10),
 ColB VARCHAR(10),
 CreatedDate DATE)
 ON PS_PartitionedTable(CreatedDate);
 
CREATE UNIQUE CLUSTERED INDEX [IX_CreatedDate_PartitionedTable] ON dbo.PartitionedTable
 (CreatedDate,PKID) 
ON PS_PartitionedTable(CreatedDate);
GO

Now inserting some data into that table: –

SET NOCOUNT ON;
INSERT INTO dbo.PartitionedTable
(ColA,ColB,CreatedDate)
VALUES
(REPLICATE('A',10),REPLICATE('A',10),'2013-02-01');
GO 1000
 
INSERT INTO dbo.PartitionedTable
(ColA,ColB,CreatedDate)
VALUES
(REPLICATE('A',10),REPLICATE('A',10),'2014-02-01');
GO 1000
 
INSERT INTO dbo.PartitionedTable
(ColA,ColB,CreatedDate)
VALUES
(REPLICATE('A',10),REPLICATE('A',10),'2015-02-01');
GO 1000
 
INSERT INTO dbo.PartitionedTable
(ColA,ColB,CreatedDate)
VALUES
(REPLICATE('A',10),REPLICATE('A',10),'2016-02-01');
GO 1000

Check the data in the partitions: –

SELECT
    t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, 
    --i.data_space_id, f.function_id, f.type_desc, 
    fg.name AS [filegroup], 
    r.boundary_id, r.value AS BoundaryValue, p.rows
    --,r.*
FROM
    sys.tables AS t
INNER JOIN
    sys.indexes AS i ON t.object_id = i.object_id
INNER JOIN
    sys.partitions AS p ON i.object_id = p.object_id AND i.index_id = p.index_id 
INNER JOIN
    sys.allocation_units a ON a.container_id = p.hobt_id 
INNER JOIN
    sys.filegroups fg ON fg.data_space_id = a.data_space_id 
INNER JOIN
    sys.partition_schemes AS s ON i.data_space_id = s.data_space_id
INNER JOIN
    sys.partition_functions AS f ON s.function_id = f.function_id
LEFT OUTER JOIN
    sys.partition_range_values AS r ON f.function_id = r.function_id 
                                    AND r.boundary_id = p.partition_number
WHERE
    t.name = 'PartitionedTable'
AND
    i.type <= 1
AND
    a.type = 1 --in row data only
ORDER BY p.partition_number DESC;

PartitionedTable1

OK, now we can truncate an individual partition by running:-

TRUNCATE TABLE dbo.PartitionedTable WITH (PARTITIONS (1));
GO

Re-checking the data: –
PartitionedTable2

And the data in partition 1 has been removed! We can also remove data from multiple partitions: –

TRUNCATE TABLE dbo.PartitionedTable WITH (PARTITIONS (2,3));
GO

Looking again: –
PartitionedTable3

And the data is gone in both the partitions. Pretty cool! Makes it a lot easier to remove data that has expired and no longer needs to be retained.

Although, it does make it a lot easier to remove data in general…what could go wrong?

(OK, I think that’s enough with the partitioning, need to look at something else…)


The DBA Who Came In From The Cold

I’m Andrew Pruski and I am a SQL Server DBA with 5 years experience in database development and administration.

The online SQL Server community has helped me out immensely throughout my career, whether from providing reference material in blog posts, or answering my (sometimes obscure) questions on forums. So, to try and say thank you, I would like to contribute my own experiences in the hope that they could benefit someone out there.

So here’s my general ramblings and thoughts about working as a SQL Server DBA.

You can find me on twitter @DBAFromTheCold

If you have any feedback on my blog please send me an email to dbafromthecold@gmail.com.

Comments

Leave a comment on the original post [dbafromthecold.wordpress.com, opens in a new window]

Loading comments...