March 22, 2016
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:
- Drop or disable the non-aligned non-clustered indexes, run the truncate, and then rebuild them.
- Don’t use truncate and write delete code instead