TRUNCATE TABLE WITH PARTITIONS: Empty One Partition Fast

TRUNCATE TABLE WITH PARTITIONS empties one or more partitions of a table and leaves every other partition alone. It needs SQL Server 2016 or later. It is the fast way to remove a month of old data.

Build a Table With Four Partitions

The demo database is named TruncatePartitionDemo. The table MonthlySales holds 40,000 sales in each of four months, January to April 2026. A partition function cuts the rows by date, and a partition scheme places them all on the PRIMARY filegroup.

The function uses RANGE RIGHT. Each boundary date starts a new partition. Partition 1 holds everything before 1 February, so it holds January. Run the script on a test server.

IF DB_ID(N'TruncatePartitionDemo') IS NULL CREATE DATABASE TruncatePartitionDemo;
GO
USE TruncatePartitionDemo;
GO
DROP TABLE IF EXISTS dbo.MonthlySales;
IF EXISTS (SELECT 1 FROM sys.partition_schemes WHERE name = N'psMonth') DROP PARTITION SCHEME psMonth;
IF EXISTS (SELECT 1 FROM sys.partition_functions WHERE name = N'pfMonth') DROP PARTITION FUNCTION pfMonth;
CREATE PARTITION FUNCTION pfMonth (date) AS RANGE RIGHT FOR VALUES ('2026-02-01', '2026-03-01', '2026-04-01');
CREATE PARTITION SCHEME psMonth AS PARTITION pfMonth ALL TO ([PRIMARY]);
CREATE TABLE dbo.MonthlySales (
    SaleID   int IDENTITY(1,1) NOT NULL,
    SaleDate date          NOT NULL,
    Amount   decimal(10,2) NOT NULL,
    Note     char(100)     NOT NULL DEFAULT 'Tea order',
    CONSTRAINT PK_MonthlySales PRIMARY KEY CLUSTERED (SaleDate, SaleID)
) ON psMonth (SaleDate);
WITH Numbers AS (
    SELECT TOP (40000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
), Months AS (
    SELECT CAST('2026-01-01' AS date) AS MonthStart UNION ALL SELECT '2026-02-01' UNION ALL SELECT '2026-03-01' UNION ALL SELECT '2026-04-01'
)
INSERT INTO dbo.MonthlySales (SaleDate, Amount)
SELECT DATEADD(DAY, n % 28, m.MonthStart), n % 100 + 0.5
FROM Numbers AS n CROSS JOIN Months AS m;

A small procedure lists the rows and pages in each partition. It reads a system view and changes nothing.

CREATE OR ALTER PROCEDURE dbo.ShowPartitions
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ps.partition_number AS Part, ps.row_count AS Rows, ps.used_page_count AS Pages
    FROM sys.dm_db_partition_stats AS ps
    WHERE ps.object_id = OBJECT_ID(N'dbo.MonthlySales') AND ps.index_id = 1
    ORDER BY ps.partition_number;
END;

EXEC dbo.ShowPartitions;

To truncate a partition, you need its number. Do not count by hand. The $PARTITION function takes a value and returns the number of the partition that holds it.

SELECT $PARTITION.pfMonth('2026-01-15') AS January,
       $PARTITION.pfMonth('2026-02-15') AS February,
       $PARTITION.pfMonth('2026-03-15') AS March,
       $PARTITION.pfMonth('2026-04-15') AS April;

Empty One Partition

February is partition 2. The statement below removes all its rows. The other three partitions are not touched.

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

EXEC dbo.ShowPartitions;

Partition 2 now has no rows and no pages. A DELETE would have removed the same rows one at a time. The pages would come back later, after the background ghost cleanup. TRUNCATE TABLE WITH PARTITIONS releases the pages at once.

Compare It With DELETE

The log shows the difference. The script below deletes March, reads the log usage of the open transaction, and rolls back. It then truncates partition 3 the same way. Both statements roll back, so the data stays. The results go into a table variable, because a rollback would remove a temp table.

DECLARE @LogUse TABLE (Method varchar(10), LogBytes bigint, LogRecords bigint);
BEGIN TRANSACTION;
DELETE FROM dbo.MonthlySales WHERE SaleDate >= '2026-03-01' AND SaleDate < '2026-04-01';
INSERT INTO @LogUse
SELECT 'DELETE', dt.database_transaction_log_bytes_used, dt.database_transaction_log_record_count
FROM sys.dm_tran_database_transactions AS dt
WHERE dt.transaction_id = (SELECT transaction_id FROM sys.dm_tran_current_transaction) AND dt.database_id = DB_ID();
ROLLBACK TRANSACTION;
BEGIN TRANSACTION;
TRUNCATE TABLE dbo.MonthlySales WITH (PARTITIONS (3));
INSERT INTO @LogUse
SELECT 'TRUNCATE', dt.database_transaction_log_bytes_used, dt.database_transaction_log_record_count
FROM sys.dm_tran_database_transactions AS dt
WHERE dt.transaction_id = (SELECT transaction_id FROM sys.dm_tran_current_transaction) AND dt.database_id = DB_ID();
ROLLBACK TRANSACTION;
SELECT Method, LogBytes, LogRecords FROM @LogUse ORDER BY LogBytes DESC;

The DELETE wrote 40,531 log records and about 8.5 MB. The truncate wrote 245 records and about 19 KB, roughly 470 times less. A truncate logs the release of whole pages, not each row. The numbers change a little from run to run, and the gap does not.

You could argue that DELETE is safer, because its WHERE clause picks the rows and the log shows each one. That is true when you remove only part of a partition. A truncate takes the whole partition. Check the number twice, and keep a backup.

The Rule About Indexes

Every index on the table must be aligned with the partitioning, or the statement fails. An aligned index uses the same partition function as the table. The script below adds an index on the PRIMARY filegroup, which is not partitioned, and tries to truncate partition 4.

CREATE NONCLUSTERED INDEX IX_MonthlySales_Amount ON dbo.MonthlySales (Amount) ON [PRIMARY];
GO
TRUNCATE TABLE dbo.MonthlySales WITH (PARTITIONS (4));

SQL Server refuses with this message.

Msg 3756, Level 16, State 1, Line 1
TRUNCATE TABLE statement failed. Index 'IX_MonthlySales_Amount' is not partitioned, but table 'MonthlySales' uses partition function 'pfMonth'. Index and table must use an equivalent partition function.

Build the index on the partition scheme instead. It then follows the table, and the truncate works. The script below does that and removes the last two months in one statement.

DROP INDEX IX_MonthlySales_Amount ON dbo.MonthlySales;
CREATE NONCLUSTERED INDEX IX_MonthlySales_Amount ON dbo.MonthlySales (Amount) ON psMonth (SaleDate);

TRUNCATE TABLE dbo.MonthlySales WITH (PARTITIONS (3 TO 4));

EXEC dbo.ShowPartitions;

The range 3 TO 4 covers both partitions. You can also mix numbers and ranges in one list, such as PARTITIONS (1, 3 TO 4).

Other Limits

A table that a foreign key references cannot be truncated, whole or in part. A table that an indexed view uses, or that replication publishes, cannot be truncated either. The script creates a child table with such a key and tries partition 1.

CREATE TABLE dbo.SaleNotes (
    NoteID   int IDENTITY(1,1) PRIMARY KEY,
    SaleDate date NOT NULL,
    SaleID   int  NOT NULL,
    CONSTRAINT FK_SaleNotes_MonthlySales FOREIGN KEY (SaleDate, SaleID) REFERENCES dbo.MonthlySales (SaleDate, SaleID)
);
GO
TRUNCATE TABLE dbo.MonthlySales WITH (PARTITIONS (1));

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'dbo.MonthlySales' because it is being referenced by a FOREIGN KEY constraint.

Drop the child table, and the key no longer blocks the truncate. A partition number outside the range fails too. Here the table has four partitions, and the request names a fifth.

DROP TABLE dbo.SaleNotes;

TRUNCATE TABLE dbo.MonthlySales WITH (PARTITIONS (5));

Msg 7722, Level 16, State 2, Line 1
Invalid partition number 5 specified for table 'dbo.MonthlySales', partition number can range from 1 to 4.

One more difference from a full TRUNCATE TABLE: the identity counter does not reset.

SELECT IDENT_CURRENT(N'dbo.MonthlySales') AS LastIdentity;

The last identity value is still 160,000, the number given to the final January to April row. New rows continue from 160,001.

Permissions are different too. A truncate needs the ALTER permission on the table, not DELETE. Give that permission to the job that purges old months, and to nobody else.

Truncate or Switch

A truncate destroys the rows. If you need to keep them, switch the partition into a staging table instead. Partition Switch in SQL Server: Move a Million Rows in a Moment shows how. A switch moves the pages without copying them, and you can archive the staging table before you drop it.

What to Remember

Use TRUNCATE TABLE WITH PARTITIONS for old data that you will not need again. Find the partition number with $PARTITION. Keep every index aligned. Remove foreign keys first, or switch the partition out instead.

When you finish with the demo, remove the database. The partition function and scheme live inside it and go with it.

USE master;
GO
DROP DATABASE TruncatePartitionDemo;

A partition is not a pile of rows, it is a unit you can remove in one step.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.