Indexing questions

Conceptual questions here...In BOL it mentions 'Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified.'. I'm wondering how this works as non-clustered indexes are built on top of clustered ones - is there in this case a significant slowdown in the speed of accessing non-clustered indexes? Also, I'm guessing here, but is the reason this might be useful is for when you want to disable and rebuild the nonclustered indexes in order to save space?
Cheers,
Paul Ibison, SQL Server MVP

Conceptually speaking, the leaf row of a non-clustered index needs to identify the data row uniquely. There are two ways to do it. First,you can use RID. The down side is that if the data row moves, the non-clustered index entry needs to be updated. Second, you can use clustered index key, if it is unique. The benefit is that if the data row moves say due to rebuild of a clustered index, nothing needs to be changed in the non-clustered index. Of course, in this case, to access data using non-clustered idnex, you will need to traverse the non-clustered index and then need to traverse the clustered index tree. Assuming that clustered index tree will be frequently accessed, it is lkely to be in the buffer pool. So you will incur few extra logical IOs per row access.

I doubt if the second method can be used to save space as it is likely that the clustered index key is longer than RID. The main benefit of the second method is that data row move does not cause the update of non-clustered index as long as the clustered key column value is not changed.

Let us see what others say.

Thanks

|||

Index rebuild (except for disabled index) does not change the logical structure of an index
(like changing a key, adding a new column, or change the sort order), it only changes the physical layout of an index on the disk. You can think of a physical index restructure, restoring

the original fillfactor and allocating the index pages in a right order removing fragmentation and compacting the index space. Therefore there is no need to rebuild the non-clustered index(es) when rebuilding a clustered index. This functionality is fully implemented in SQL Server 2005, and was already partially implemented in SQL Server 2000. We have added the keyword ALL to make sure customers can rebuild all indexes for a given table using one command (ease of use purpose). Clustered or non-clustered index rebuild may increase performance if the index is fragmented, especially when queries use

index scans.
Disabling a clustered index does not really save space. We still need to keep the old clustered index (marked as disabled) to make sure we can rebuild it. Disabling a non-clustered (NC) index brings some space benefit since we drop the old index and keep only its metadata, so when we rebuild an NC index we use either the base table or a clustered index. However this benefit can be easily reached by using two commands DROP NC index and CREATE NC index.

Thanks,

Mirek

|||Thanks Sunil,
this makes perfect sense. I hadn't seen it in this way before - if rebuilding the clustered index is only done to reorder the data (not to remove fragmentation) then there might be no performance gain from also rebuilding the non-clustered index, as the indexes are unlikely to be ordered in the same way.
Cheers,
Paul|||Thanks Mirek,
this makes sense. So in some cases where the table is large (disk space small) it might be useful to rebuild the clustered index without the ALL option, then subsequently disable and rebuild each non-clustered index.
Cheers,
Paul