We have a COTS product that has created a very large transaction log table. It has a single clustered index which supports an IDENTITY column declared as a key. Last night the index got rebuilt and the server ran out of disk space so I've been up since 12:30 am.
Now I'm wondering does SQL Server even do anything when it rebuilds this index? There are no leaf pages or page splits since all the inserts go into the last page. And we never delete rows (not yet anyway).
Obviously the server tried to do something, but if it had succeeded would the table be any different? I am thinking this is just a giant no-op and a waste of time. Obviously this is not true if the table has non-clustered indexes as well. And actually if this table did have additional indexes it still doesn't NEED to rebuild the primary index first if like our table it was all just adds, right?
My DBA says he manages all the indexes at the database level, not at the table level.
Additional information
My assumption is that after the rebuild the pages on disk look the same as they did before and we're just wasting our time database shuffling.
Best Answer
Since you don't delete rows, no, rebuilding the index will provide no real value outside of updating the statistics for said index, as they are implicitly updated with a rebuild operation. A more efficient approach is to just run an
UPDATE STATISTICSstatement instead.Per Benjamin Nevarez:
To answer your other questions:
Other than located at a different page address, no, structurally it would likely be no different.
Rebuilding the clustered index doesn't automatically rebuild any non-clustered indexes associated with the table. To do this, you would need to specify the keyword
ALLinstead of the clustered index name. Per MS:And the last question:
Correct. If you never modify a record after it's added to the end of the table, you'd really only ever need to update statistics in order to avoid the Ascending Key Problem as identified by Scott Hodgin's comment below.