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.

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

SQL Server 2016 – truncation of specific partition

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:

SQL Server 2016 - Truncate Table with Partition - 2

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

SQL Server 2016 - Truncate Table with Partition - 3

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:

SQL Server 2016 - Truncate Table with Partition - 4

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:

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:

SQL Server 2016 - Truncate Table with Partition - 5



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