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

speakingInVA

Blast from the past – teaching table partitioning in Virginia back in 2010.

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.

Previous Post
Learn Indexing from Kendra in Huntington Beach on April 1 for $99!
Next Post
3 Tricks with STATISTICS IO and STATISTICS TIME in SQL Server

Related Posts

1 Comment. Leave new

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu