SQL Server 2016: Introduce New TRUNCATE by Partitions Number

In this post, I am sharing a new version of TRUNCATE command to truncate base on partitions, and this newly TRUNCATE introduced in SQL Server 2016.

Always we keen like, why we cannot apply WHERE condition or any other filter in TRUNCATE Command. TRUNCATE command is faster than delete because it’s not required more resources like DISK I/O and Transaction LOG.

Now with the SQL Server 2016 you can perform TRUNCATE base on Partition Number.
If you have a big size of the table and you already applied proper partitions on that table, You can use the new TRUNCATE command.

Below is a full demonstration of this New TRUNCATE.

CREATE SAMPLE DATABASE:

ADD File Group to New Database:

Create .ndf file to store partitions data and attached to perticular filegroup:

Create Partition functions:

Create Partition scheme:

Create Sample table:

Insert Sample data:

Script to check, if the data exist in Partitions:

Final step to TRUNCATE Partitions by Partition Number:

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of
avatar
wpDiscuz