Select Page

SQL Server Partitioning an Active Table with Truncate

Author: Eric Russo | | September 1, 2016

There are a number of interesting capabilities that get added in almost every new version of SQL Server for performance and scalability needs. As these features get added, they are often underappreciated and sometimes hardly used at all by many developers. One such feature that I have been tracking for close to ten years is Partitioning with SQL Server. When it first surfaced in SQL Server 2005, there was enough hype around this capability that it matured, eventually becoming one of the silver bullets I use with my customers. For mission critical large databases, this is a killer feature everyone should explore.

If you ask me, when SQL Server 2016 was released, I didn’t think there was much that could be done to enhance the capabilities of Partitioning. But in the most recent version, there has been an interesting addition that was added –the ability to Truncate a specific partition.

Why is this important? The premise for this feature is that we have the unique ability to partition data based on some state / date range. Many times it is important to remove unwanted data that has become stale after a period of time. A typical example is to remove the transaction records from a table which contains data more than three years old. If these data are not used or can ever change, DBAs always want an easy way to remove these records without using the DELETE statement. This is because the DELETE statement would bloat the Log files and if the amount of data is large, the table can easily get locked out blocking queries running on the table.

This is where partitioning comes in. Personally, I see this as an awesome addition to SQL Server 2016 that makes such critical processes seamless from an archival / removing dead data requirements.

Let me show you the construct you can use to visualize how this works. Here, I am showing you the number of rows in various partitions and then after the truncate statement, we can see only a specific partition rows have been cleared.

SELECT partition_id, partition_number,rows

FROM sys.partitions

WHERE OBJECT_NAME(OBJECT_ID)='Partioned_Table';

GO

-- Works from SQL 2016 only

TRUNCATE TABLE dbo.Partioned_Table WITH (PARTITIONS(2));

--- Verify Rows Deleted in Partitions

SELECT partition_id, partition_number,rows

FROM sys.partitions

WHERE OBJECT_NAME(OBJECT_ID)='Partioned_Table';

GO

The output for this would look like:

Truncate Table

In this example, we have cleared just a single partition. The same can be done to clear multiple partitions using the following construct.

TRUNCATE TABLE dbo.Partioned_Table WITH (PARTITIONS(1,2,3,4));

If you have continuous partitions to be cleared, we can do this by using a range type of construct. A typical script for this would look like:

TRUNCATE TABLE dbo.Partioned_Table WITH (PARTITIONS(1 TO 4));

As you can see there are a number of ways we can use the TRUNCATE with Partitioned Tables in SQL Server 2016. I am sure you will find it useful and use it in your environments too, as you upgrade to SQL Server 2016.

Datavail Script: Terms & Conditions

By using this software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS