3

Assume that I have a heap table with a higher fragmentation percentage and that table also has a non-clustered index with higher fragmentation too. I can't reproduce this case right now but maybe someone had tested it before.

Do I need to rebuild a heap table after rebuilding a non-clustered index?

4
  • Table rebuilding forces non-clustered indexes be rebuilt as well. So, if you need to rebuild a non-clustered index and a heap data you'll have to perform ALTER TABLE YourTable REBUILD;. – Denis Rubashkin Dec 17 '19 at 11:33
  • But what if I only rebuild a non-clustered index? Do I have to then rebuild the heap table? – RaufDBA Dec 17 '19 at 11:39
  • 1
    If you have to get rid of fragmentation on your heap, you have to rebuild the heap. Your non-clustered index will be rebuilt twice. Non-clustered index rebuilding doesn't touch its heap nor clustered index. – Denis Rubashkin Dec 17 '19 at 11:47
  • 2
    Heaps by nature are fragmented, is fragmented heap affecting your query performance ?, if not, leave it as it is. Best way to get rid of heap fragmentation is to create a clustered index ( not a constraint though if you are happy with heap). – Shanky Dec 17 '19 at 12:57
2

Do I need to rebuild a heap table after rebuilding a non-clustered index?

Rebuilding a heap automatically rebuilds any non-clustered indexes on the table (because the pointers to where the rows are changes during this process).

I would not rebuild a heap after rebuilding the NC index, because this would mean the NC index is rebuilt twice (which is just wasteful).

Rebuilding the NC index won't fix fragmentation in the heap. So if you need to fix the heap fragmentation, rebuild that first (or put a clustered index on it), and both will be fixed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.