Truncate Table with Partitions Fails if You Have Non-Aligned Indexes

SQL Server 2016 brought in a cool new little feature for table partitioning: you can now truncate individual partitions. There’s one little gotcha, though: you can only do this if all the indexes on the tables are “aligned”.

Here’s what the syntax looks like:

TRUNCATE TABLE dbo.FirstNameByBirthDate_pt WITH (PARTITIONS (4));
go

Meet Error Msg 3765

If you have a non-aligned index on the table, you’ll see an error like this:

Msg 3756, Level 16, State 1, Line 1 TRUNCATE TABLE statement failed. Index ‘ix_FirstNameByBirthDate_pt_BirthYear_FirstNameId_nonaligned’ is not partitioned, but table ‘FirstNameByBirthDate_pt’ uses partition function ‘pf_fnbd’. Index and table must use an equivalent partition function.

This isn’t a bug, and it makes total sense from a logical point of view. “Non-aligned” indexes are not partitioned like the base table is– by definition they are either partitioned differently, or not partitioned at all. The chunk of data that you’re trying to truncate isn’t all in an easily identifiable partition that can be quickly marked as “data non grata”. There’s just no way to do a simple truncate when the data’s scattered all around.

If you hit this message, you have two choices:

1. Drop or disable the non-aligned non-clustered indexes, run the truncate, and then rebuild them.
2. Don’t use truncate and write delete code instead

This is fully documented in Books Online, (thanks to a Connect Item by John Sterrett) but it’s one of those little details that I just hadn’t thought about before playing around with the new feature.