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?

Improve this question
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, 2019 at 11:33
  • But what if I only rebuild a non-clustered index? Do I have to then rebuild the heap table?
    – Rauf Asadov
    Dec 17, 2019 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, 2019 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, 2019 at 12:57

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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