How to Truncate Specific Partitions in SQL Server (Fast & Efficiently)

Before You Begin:

• Ensure you are on SQL Server 2016 or higher.
• Verify that your table is Partitioned.
• Backup Warning: While TRUNCATE is fast, it is not easily reversible without a log backup or snapshot.

Truncate Table WITH Partitions SQL Server

It is a well known fact that TRUNCATE TABLE is faster than DELETE statement. TRUNCATE TABLE SQL Server uses lesser system resources and transaction log file when compared to a DELETE statement.

Starting SQL Server 2016, Microsoft has introduced a new clause TRUNCATE TABLE WITH PARTITIONS SQL Server which can be used to truncate all rows available within a partition.

Comparison Table for DELETE Statement Vs SWITCH PARTITION Vs TRUNCATE PARTITION

Syntax: TRUNCATE TABLE WITH PARTITIONS

TRUNCATE TABLE [SchemaName].[TableName] 
WITH (PARTITIONS [Partition Number Expression] | [Range]);

Before you use TRUNCATE TABLE WITH PARTITION clause in SQL Server 2016 make sure all the indexes on the table are aligned with its partitions else the statement will fail.

TRUNCATE TABLE vs SWITCH PARTITION Performance

Historically, clearing data required SQL Server partition switching, a complex process involving creating a staging table, matching indexes exactly, and executing multiple metadata changes.

In contrast, TRUNCATE TABLE WITH PARTITIONS simplifies your workflow into a single, high-performance T-SQL command. It bypasses the need for temporary objects while maintaining the benefits of a minimally logged operation. This drastically reduces transaction log bloat and execution time, offering a more efficient alternative to the “Switch-Truncate-Drop” pattern for large-scale data lifecycle management.

Example Truncate Table with Partitions SQL Server

Execute the below sample code to remove Partitions 12, 14, 16, 17 and 18 in SQL Server 2016 using TRUNCATE TABLE WITH PARTITIONS SQL SERVER clause.

TRUNCATE TABLE Sales.TransactionHistory 
WITH (PARTITIONS (12, 14, 16 TO 18))
GO

Troubleshooting Aligned Indexes

Why does TRUNCATE TABLE WITH PARTITIONS fail?

The most common cause of failure is the presence of a non-aligned index. For this T-SQL enhancement to work, every non-clustered index must be created on the same partition scheme and partition function as the base table. If even one “global” index exists that isn’t partitioned, SQL Server will block the operation to maintain index integrity.

The Error Message:

Msg 3756, Level 16, State 1, Line 10 TRUNCATE TABLE statement failed because the table 'SalesHistory' has one or more non-aligned indexes.

How to Fix the Alignment Issue

To resolve this and successfully truncate your partition, you have three options:

1. Align the Index: Drop and recreate the non-clustered index using the same partition scheme as the table.
2. Disable the Index: Disable the non-aligned index before truncating and rebuild it afterward (though this may be slow for large datasets).
3. Drop the Index: Permanently remove any non-essential global indexes that do not support the partitioning strategy.

The “Find Partition” Query: The code to find partition numbers

How to Identify Your Partition Number Before truncating, use this query to verify which partition ID corresponds to your data ranges:

SELECT 
    p.partition_number, 
    p.rows, 
    rv.value AS BoundaryValue
FROM sys.partitions p
LEFT JOIN sys.partition_range_values rv 
    ON p.partition_number = rv.boundary_id
WHERE p.object_id = OBJECT_ID('YourTableName');

Frequently Asked Questions (FAQs) on How to Truncate Specific Partitions in SQL Server (Fast & Efficiently)

1. Does TRUNCATE TABLE WITH PARTITIONS reset the Identity column?

Unlike a full table truncate, truncating a specific partition in SQL Server does not reset the IDENTITY seed value. The identity counter will continue to increment from the last global value. If you need to reset the seed, you must truncate the entire table or use DBCC CHECKIDENT.

2. Can I truncate multiple partitions in a single command?

Yes. SQL Server 2016 allows you to truncate multiple individual partitions or a range. For example, WITH (PARTITIONS (1, 3, 5 TO 7)) will clear partitions 1, 3, 5, 6, and 7 simultaneously.

3. Why do I get Msg 3756 when truncating a partition?

This error occurs because of non-aligned indexes. For a partition truncate to work, all non-clustered indexes must use the same partition scheme and function as the base table. If a “Global Index” exists that covers the whole table without partitioning, the command will fail.

4. Is TRUNCATE TABLE PARTITION faster than SWITCH PARTITION?

While both are metadata-only operations and nearly instantaneous, TRUNCATE TABLE WITH PARTITIONS is simpler. It removes the need to create a temporary staging table, manage index alignment on that staging table, and then drop it, saving you several lines of script and manual effort.

5. Does truncating a partition require a full table lock?

TRUNCATE TABLE always acquires a Schema Modification (Sch-M) lock on the table. While the data deallocation happens at the partition level, the Sch-M lock is global and will briefly prevent other sessions from accessing the table schema or performing DDL operations until the command completes.

SQL Server 2016 T-SQL Enhancement Series

• DROP IF EXISTS
• ALTER TABLE WITH (ONLINE = ON | OFF)
• MAXDOP for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP
• ALTER DATABASE SET AUTOGROW_SINGLE_FILE
• ALTER DATABASE SET AUTOGROW_ALL_FILES
• COMPRESS and DECOMPRESS Functions
• STRING_SPLIT and STRING_ESCAPE Functions
• FORMATMESSAGE Statement
• SERVERPROPERTY Function
• TRUNCATE TABLE WITH PARTITIONS

Trending SQL Server Articles and Tips