SQL Server 2016 – truncation of specific partition

Hi Friends,

Till now we have seen various new improvements in SQL Server 2016. In my previous blog posts, I had explained Trace Flag 1117 and 1118 related changes. Today I am going to show you an another improvement in SQL Server 2016 i.e. truncation of specific partition or the number of partitions.

Before SQL Server 2016, if we want to delete the number of rows from specific partitions, then we were using the delete command. Delete and truncate both have their pros and cons. I am not going to discuss that here. It depends on the requirement which command you want to use. In SQL Server 2016, if you want to truncate a single partition or multiple partitions, then you can do that by using the simple TSQL statement. Let me show you the same thing practically.

In the below TSQL, I am going to create a database which has primary file group and index file group. I’ll create a partition table over partition scheme.

--Create a database for Demo at default files location
USE [master]
go
CREATE DATABASE [PartitionTruncate]
 ON  PRIMARY 
( NAME = N'PartitionTruncate', FILENAME = N'D:\Test\PartitionTruncate.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB ), 
 FILEGROUP [INDEXES] 
( NAME = N'PartitionTruncateIndexes', FILENAME = N'D:\Test\PartitionTruncateIndexes.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'PartitionTruncate_log', FILENAME = N'D:\Test\PartitionTruncate_log.ldf' , SIZE = 204800KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
go
Use PartitionTruncate
go
--Create Partition Function
create partition function xpfPartitionTruncate (int)
as range right for values
(10000,20000,30000,40000,50000,60000,70000)
go
--Create Partition Scheme
create partition scheme xpsPartitionTruncate
as partition xpfPartitionTruncate
ALL to ([Primary])
go
--Create table by using Partition Scheme on id column
create table xtPartitionTruncate
(
id int not null,
balance int not null,
name varchar(25)
) on xpsPartitionTruncate (id)
go
--Create a clustered index on id column [This will cover complete table]
create clustered index CI_xtPartitionTruncate on xtPartitionTruncate (id);
go

Now I am going to insert the data into all the created partitions:

-- Now I am inserting 80000 rows in the table. That means each partition will contain approx 10000 rows here
-- Here id and balance both are having same values for demo purpose
set nocount on
declare @counter int =1
declare @balance int =1
while (@counter<=80000)
begin
    if (@counter%2=0)
        begin
        insert into xtPartitionTruncate values(@counter,@balance,'data1')
        end
    else
        begin
        insert into xtPartitionTruncate values(@counter,@balance,'data2')
        end
    set @counter = @counter + 1;
    set @balance = @balance + 1;
end
go
--You can check here about how many partitions with number of rows belongs to the table
select * from sys.partitions where object_id=OBJECT_ID('xtPartitionTruncate')
go

In the above image, you can see that we have 8 partitions and number of rows in each partition. Now, I’ll truncate the first partition using below TSQL:

TRUNCATE TABLE xtPartitionTruncate 
WITH (PARTITIONS (1));
go
--You can check here about how many partitions with number of rows belongs to the table
select * from sys.partitions where object_id=OBJECT_ID('xtPartitionTruncate')
go

What if we want to truncate multiple partitions at a same time. Lets try that by truncating the partition 2 and 3:

TRUNCATE TABLE xtPartitionTruncate 
WITH (PARTITIONS (2,3));
go
--You can check here about how many partitions with number of rows belongs to the table
select * from sys.partitions where object_id=OBJECT_ID('xtPartitionTruncate')
go

Is that means, if I want to truncate the multiple partitions, then I’ll specify all of them by a comma separated list? No, if they belong to the range then we can specify the partition range as mention below:

TRUNCATE TABLE xtPartitionTruncate 
WITH (PARTITIONS (4 to 7));
go
--You can check here about how many partitions with number of rows belongs to the table
select * from sys.partitions where object_id=OBJECT_ID('xtPartitionTruncate')
go

One important limitation is also there, if you have non align indexes, then you can not truncate the partitions. Let me show you by creating a non clustered non align index and then I’ll try to truncate the partition number 8:

USE [PartitionTruncate]
go
CREATE NONCLUSTERED INDEX [NCI_Balance_xtPartitionTruncate] ON [dbo].[xtPartitionTruncate]
(
	[balance] ASC
) 
ON [INDEXES]
go
TRUNCATE TABLE xtPartitionTruncate 
WITH (PARTITIONS (8));
go

Msg 3756, Level 16, State 1, Line 88

TRUNCATE TABLE statement failed. Index ‘NCI_Balance_xtPartitionTruncate’ is not partitioned, but table ‘xtPartitionTruncate’ uses partition function ‘xpfPartitionTruncate’. Index and table must use an equivalent partition function.

If you have align indexes, then you will not face this issue. Let me create an align index and then try to truncate the partition number 8:

Drop Index [dbo].[xtPartitionTruncate].[NCI_Balance_xtPartitionTruncate]
go
CREATE NONCLUSTERED INDEX [NCI_Balance_xtPartitionTruncate] ON [dbo].[xtPartitionTruncate]
(
	[balance] ASC
) 
go
TRUNCATE TABLE xtPartitionTruncate 
WITH (PARTITIONS (8));
go
--You can check here about how many partitions with number of rows belongs to the table
select * from sys.partitions where object_id=OBJECT_ID('xtPartitionTruncate')
go

HAPPY LEARNING!

Regards:

Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook