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.


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:

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of