DELETE vs. TRUNCATE in SQL Server: Plan Your Partitions So Purging Old Data Costs Almost Nothing

What DELETE Does

DELETE is a row operation. For every qualifying row SQL Server:

• removes the row from the clustered index (or heap) and from every nonclustered index,
• writes a log record for each of those changes, with enough information to undo it,
• reserves additional log space for the rollback, in case the transaction fails,
• takes row or page locks and, after about 5,000 locks, escalates to a lock on the whole table,
• fires DELETE triggers, checks foreign keys, feeds replication, CDC and every AG secondary with the full change stream,
• leaves ghost records behind that a background task has to clean up afterwards.

The cost grows linearly with the number of rows and the number of indexes. A WHERE clause can be as selective as you like: it is still one log record per row per index.

What TRUNCATE Does

TRUNCATE TABLE does not touch rows at all. It deallocates the pages and extents that belong to the table and logs only those deallocations. The actual freeing of large allocations is deferred to a background task. The cost depends on the amount of allocated space, not on the number of rows, and it is tiny either way.

Two myths worth killing right away:

• "TRUNCATE is not logged." Wrong. It is minimally logged: the deallocations are logged. That is why it works in every recovery model, including FULL, and why log backups and AG replicas stay consistent.
• "TRUNCATE cannot be rolled back." Wrong. Inside an explicit transaction, a ROLLBACK restores everything. I verified this in the test below: truncate a 100,000-row partition, roll back, count, 100,000 rows are back.

Measured: 100,000 Rows, Four Ways

Test setup: SQL Server 2022 Developer Edition, database in FULL recovery, a table with 1.2 million rows (twelve months, 100,000 rows per month, about 220 bytes per row), a clustered primary key on (OrderDate, OrderId) and one nonclustered index. Once as a plain table, once partitioned by month with all indexes aligned. Each operation ran in its own transaction; log values are read from sys.dm_tran_database_transactions before COMMIT.

Split into its parts in a second run, the DELETE of one month used 47.8 MB of log and reserved another 119.8 MB for a possible rollback. The partition TRUNCATE used 17 KB and reserved 35 KB. That is a factor of more than 2,500 on log actually written, and the reservation is what makes the log file grow during a big purge.

Three things stand out:

1. Partitioning alone makes DELETE no cheaper. 163 MB versus 167 MB. A partitioned table only pays off for purging if you stop deleting and start truncating or switching.
2. The durations at this size say little. 100,000 rows is small, both variants finish in about a second. The log volume is the real story, because it scales: 100 million rows means roughly 48 GB of log written for the DELETE plus another 120 GB reserved while it runs, every written byte shipped to every AG secondary and included in the next log backup. The TRUNCATE stays in the kilobyte range.
3. Even on the partitioned table, DELETE locked the whole table. Lock escalation goes to the table by default, not to the partition. More on that below.

TRUNCATE TABLE ... WITH (PARTITIONS)

Since SQL Server 2016, TRUNCATE can target individual partitions. The syntax accepts single numbers, lists and ranges:

-- one partition
TRUNCATE TABLE dbo.OrderHistory WITH (PARTITIONS (4));

-- several, mixed list and range
TRUNCATE TABLE dbo.OrderHistory WITH (PARTITIONS (2, 4, 6 TO 8));

-- by value instead of by number: the partition that holds July 2026
TRUNCATE TABLE dbo.OrderHistory WITH (PARTITIONS ($PARTITION.pf_month('2026-07-15')));

The $PARTITION form is the one to use in scripts. Partition numbers shift whenever a boundary is merged, so a hard-coded number from last month can point at the wrong data today. Asking the partition function "which partition holds this date" is always correct.

The requirements

• Every index must be aligned, meaning built on the same partition function as the table. One forgotten nonclustered index on [PRIMARY] and the statement fails:

  TRUNCATE TABLE statement failed. Index 'IX_NotAligned' is not partitioned,
  but table 'OrderHistory' uses partition function 'pf_month'. Index and table
  must use an equivalent partition function.

• No foreign key may reference the table, not even an empty one, and even if no child row points into the partition you want to drop. Same error as for a plain TRUNCATE.
• The other TRUNCATE restrictions apply as well: no indexed views on the table, no transactional or merge replication, no CDC, no system-versioned temporal table.

Identity is not reset

A plain TRUNCATE TABLE resets the identity column to its seed. A partition truncate does not, which is exactly what you want for a sliding window: in the test, the plain table started again at 1, the partitioned table kept its current value of 1,200,000 after three partition truncates.

The catch: Sch-M on the whole table

Two consequences for planning: run purges in a quiet window, and keep the purge transaction short. Do not wrap a truncate into a long transaction together with other work, because the Sch-M lock is held until COMMIT.

TRUNCATE TABLE has no WAIT_AT_LOW_PRIORITY option (SQL Server 2022 rejects it with a syntax error). ALTER TABLE ... SWITCH does have it, which is one of the reasons to prefer SWITCH on busy systems:

ALTER TABLE dbo.OrderHistory
    SWITCH PARTITION 2 TO dbo.OrderHistory_Stage
    WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF));

With ABORT_AFTER_WAIT = SELF, the switch waits in a low-priority queue, gives up after the duration and leaves the blocking session alone. In the test it did exactly that: it gave up after 60 seconds, the reader's transaction was untouched. The job can retry later instead of blocking the application.

TRUNCATE or SWITCH?

Both are metadata operations, both need aligned indexes and both take Sch-M. The difference:

• TRUNCATE ... WITH (PARTITIONS) is one statement, needs no staging table, and is the right choice when the data simply has to go.
• SWITCH moves the partition into an empty staging table with identical structure on the same filegroup. From there you can copy it to an archive database, export it, or check it, and only then truncate and drop the staging table. It also supports WAIT_AT_LOW_PRIORITY.

Neither of them removes the partition itself. After the purge, the boundary is still there and the partition is empty. To keep the partition count from growing forever (the limit is 15,000), merge the now-empty boundary away. On an empty partition, MERGE RANGE is a metadata operation as well.

Designing a Table for TRUNCATE

All of this only works if the table was built for it. Partitioning is not a switch you flip when the purge job runs too long. These are the decisions that make the difference.

1. Partition by the column your retention rule uses

If the rule is "keep 36 months by booking date", the partition column is the booking date. Not the insert timestamp, not the ID. Only then does one partition contain exactly the rows that expire together. The partition column must be part of the clustered index key and of every unique index, so this decision affects the primary key. Make it early.

2. Granularity = purge unit

Monthly retention, monthly partitions. If you partition by year but purge monthly, you are back to DELETE for eleven months of the year. Finer than necessary is fine (monthly partitions, yearly purge truncates twelve at once), coarser is not.

3. Align every index, and keep it that way

Create every nonclustered index ON ps_month(OrderDate). The trap is not the initial design but the index someone adds two years later through the GUI, which lands on [PRIMARY] and silently breaks the next purge run. Check alignment regularly:

SELECT i.name, i.type_desc, ds.type_desc AS data_space
FROM sys.indexes i
JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
WHERE i.object_id = OBJECT_ID('dbo.OrderHistory')
  AND ds.type_desc <> 'PARTITION_SCHEME';   -- every row returned here breaks TRUNCATE/SWITCH

4. Keep empty partitions at both ends

SPLIT RANGE and MERGE RANGE are only cheap when the affected partitions are empty. Splitting a partition that already contains data physically moves rows and logs every one of them, which is exactly the cost you wanted to avoid. So:

• use RANGE RIGHT for date boundaries, so each boundary is the first day of its period,
• always create the next periods before data arrives (a buffer of a few empty future partitions),
• keep the leftmost partition empty, so merging the oldest boundary never moves data.

5. Think about foreign keys before you need the purge

Any foreign key that references the table blocks TRUNCATE and SWITCH. If child tables exist, partition them by the same column and purge them first, or enforce the relationship in a different way. Discovering this on the night of the first purge is the most common reason the "fast" plan falls back to DELETE.

The resulting sliding window

-- 1. Add next month's empty partition in advance (cheap: target is empty)
ALTER PARTITION SCHEME ps_month NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pf_month() SPLIT RANGE ('2027-02-01');

-- 2. Purge the expired month (metadata only)
DECLARE @p int = $PARTITION.pf_month('2023-10-01');
TRUNCATE TABLE dbo.OrderHistory WITH (PARTITIONS (@p));

-- 3. Remove the now-empty boundary (cheap: partition is empty)
ALTER PARTITION FUNCTION pf_month() MERGE RANGE ('2023-10-01');

Three statements, kilobytes of log, independent of how many rows the month had. Run it monthly from an Agent job and the retention problem is gone. In practice, the job also has to verify the partition is really empty before the merge, handle boundary types other than date, deal with locking and log what it did. That is where the tooling comes in.

If DELETE Cannot Be Avoided

Sometimes the rule does not match the partitioning, for example a GDPR deletion of one customer across all months. Then two things help:

• Delete in batches (for example DELETE TOP (5000) ... WHERE ... in a loop with a log backup or checkpoint in between). This keeps each transaction small, the log reusable and lock escalation from kicking in.
• LOCK_ESCALATION = AUTO on a partitioned table escalates to the partition (HoBT) instead of the whole table. In the test, a DELETE in January with AUTO held only partition-level X locks, and a reader of March was not blocked. With the default TABLE, the same reader ran into the lock timeout. Be aware that partition-level escalation can cause deadlocks between sessions working on different partitions, so test it under real load before making it the default.

Getting There: sqmPartitionTool

Designing a new table for TRUNCATE is easy. The hard part is the existing table with 500 million rows, no partitioning, a primary key on an identity column and three nonclustered indexes. That is the situation sqmPartitionTool was built for: a free PowerShell module (with GUI) that takes a table from "unpartitioned" to "sliding window runs by itself", based on dbatools.

It maps directly to the planning steps above:

• Get-sqmPartitionCandidateTable lists the tables in a database that are worth partitioning, and Get-sqmPartitionColumnCandidate suggests suitable date columns.
• Test-sqmPartitionReadiness checks a table before you touch it: a usable data type for the partition column, a primary key or unique clustered index that does not contain the partition column (the key change from step 1), referencing foreign keys that would block SWITCH later, and enough free space for the rebuild.
• Invoke-sqmTablePartitionConversion converts the existing table: creates the partition function and scheme with the chosen granularity (month, quarter, year) and rebuilds the clustered index on the scheme, including the key change if you allow it. For very large tables or tight disk space there are copy-based methods that move the data segment by segment. Date columns as well as int or text surrogate keys like yyyyMMdd are supported. Nonclustered indexes that are not aligned afterwards are reported, not silently rebuilt, because filtered or unique indexes need a conscious decision.
• Test-sqmPartitionIndexAlignment finds the index that was added later on the wrong filegroup, before it breaks the purge.
• Register-sqmPartitionTable records the table with its retention rule, for example 36 months, and an optional archive database.
• New-sqmPartitionExtendJob creates the Agent job that keeps empty future partitions available (step 4 above), New-sqmPartitionRetentionJob the job that removes expired ones.

The retention side uses the SWITCH route described above: the expired partition is switched into a staging table, optionally copied to the archive database, the staging table is truncated and dropped, and after a defensive check that the partition is really empty, the boundary is merged away. So the partition disappears completely instead of staying behind as an empty shell. For an ad-hoc cleanup ("we need the space now"), Invoke-sqmPartitionRetention does the same for one table immediately:

# Convert an existing table to monthly partitions
Invoke-sqmTablePartitionConversion -SqlInstance SQL01 -Database Sales -Schema dbo `
    -Table OrderHistory -PartitionColumn OrderDate -Granularity Month

# Register it with a 36-month retention and an archive target
Register-sqmPartitionTable -SqlInstance SQL01 -Database Sales -Schema dbo -Table OrderHistory `
    -PartitionColumn OrderDate -PartitionFunctionName PF_OrderHistory_OrderDate `
    -PartitionSchemeName PS_OrderHistory_OrderDate -Granularity Month -BoundaryType Date `
    -RetentionValue 36 -RetentionUnit Months -ArchiveEnabled -ArchiveDatabaseName SalesArchive

# Instance-wide jobs: extend the window, apply retention
New-sqmPartitionExtendJob    -SqlInstance SQL01
New-sqmPartitionRetentionJob -SqlInstance SQL01

# One-off: remove everything older than 10 years right now
Invoke-sqmPartitionRetention -SqlInstance SQL01 -Database Sales -Schema dbo `
    -Table OrderHistory -RetentionValue 10 -RetentionUnit Years

Source, changelog and admin handbook are on GitHub. For the partitioning basics (functions, schemes, filegroups, partition elimination) see Table Partitioning for Large Tables.

The Bottom Line

DELETE removes rows, TRUNCATE removes pages. For 100,000 rows that is 163 MB of log versus 39 KB, and the gap grows with every row. Partitioning does not make DELETE any cheaper: it gives you the unit that TRUNCATE and SWITCH can work on. To get there, partition by the retention column at the granularity of your purge, keep every index aligned, keep empty partitions at both ends, and get foreign keys out of the way. Then retention is three metadata statements a month, with one thing left to respect: the short Sch-M lock on the whole table. If your tables are not built that way yet, sqmPartitionTool does the conversion and runs the sliding window for you.