Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm having trouble finding a definitive resource on this anywhere, so hopefully a guru can give me an answer here.

I have a very large table that we had to add a column to. The clustered index is pretty highly fragmented, and I want to do an ALTER INDEX REBUILD to clean it up.

I also normally do an ALTER TABLE REBUILD when I change the columns since this cleans up any pointers or splits from that operation.

Do I need to do both since we are talking about a the clustered index, which is essentially the table?

My suspicion is the ALTER INDEX REBUILD on the clustered will not update everything that the ALTER TABLE will, but I also am afraid that the ALTER TABLE won't clean up the index fragmentation.

share|improve this question
    
IIRC rebuild won't update column statistics. Doubt that is relevant since it won't happen in either case. :-) –  Aaron Bertrand Apr 18 '12 at 20:27
add comment

2 Answers

up vote 4 down vote accepted

If you rebuild the clustered index there shouldn't be any need to rebuild the table. If the table was a heap (didn't have a clustered index) then you might want to rebuild it.

share|improve this answer
    
I read previously that when you remove a column, you need to do an ALTER TABLE REBUILD since this will fix pointers not fixed in the index rebuild. Is this no longer the case? –  JNK Apr 18 '12 at 20:05
    
What pointers? I've never heard that. Removing a column doesn't have any pointers, unless you are talking about TEXT, NTEXT, IMAGE data types in SQL 2000. –  mrdenny Apr 18 '12 at 20:11
1  
I think my concerns are unfounded then... –  JNK Apr 18 '12 at 20:15
    
@JNK: I suspect you mean DBCC CLEANTABLE when droppimg columns which is the same as rebuilding the clustered index stackoverflow.com/a/808368/27535 and dba.stackexchange.com/search?q=%2BDBCC+%2BCLEANTABLE –  gbn Apr 19 '12 at 8:13
add comment

Rebuild drops and recreates the index. When you create a clustered index, it reorders the rows of a table in addition to creating the CIX (a table with no CIX is a heap).

share|improve this answer
2  
Rebuild does not drop and recreate the clustered index. Dropping the clustered index turns the table into a heap. IF the rebuild would 'drop and recreate' there would have to be an intermediate state in which the table is a heap, which is not true. If you don't believe me that there is no intermediate heap stage, consider that had such intermediate heap state be achieved the 'recreate' would require a sort, but a quick look at the rebuild execution plan reveals there is no sort. –  Remus Rusanu Apr 18 '12 at 23:37
    
Best regards to you RR, I enjoy reading your posts, but I'm not sure why you would argue this point. I already linked the official MSDN. From the "Rebuilding Indexes" section about half way down, 1st statement: "Rebuilding an index drops and re-creates the index." There is no ambiguity in that statement. Here it is again: msdn.microsoft.com/en-us/library/ms188388.aspx –  Eric Higgins Apr 19 '12 at 16:13
    
BOL and MSDN may have poor choice of wording form time to time, and then time and time again... This would be such a case. –  Remus Rusanu Apr 19 '12 at 17:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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