Reorganization of Non-Clustered Indexes

We all have seen people getting confused on the indexes reorganization processes. One of doubts that often popup is that whether the clustered index reorganization of a table will automatically reorganize all the non-clustered indexes on the table. Well, the answer is that it depends on the version and on the type of the clustered index column.In case of SQL server 2005, the answer is straight forward – the non-clustered indexes will NOT be reorganized at all. If the version is SQL Server 2000, it depends on the clustered index. Let us discuss SQL Server 2000 first. In SQL Server 2000, if the table is a heap, then the non-clustered indexes will locate the row corresponding to a key, based on the physical recordid which consists of the pageid and the record slot. This is a physical value. So in a heap, the lookup reference is a physical location. In case we create a clustered index on the heap or remove the clustered index on a table, these physical locations may get changed due to the rearrangements. Hence, the lookup references for the non-clustered indexes will become invalid which means the non-clustered indexes need to be reorganized.
Now, let us consider that the table is not a heap and has a clustered index. In case the table has a clustered index, the lookup is NOT based on the physical recordid. This is because if there is a clustered index, the lookup can uniquely identify the record using the clustered index key. This is logical reference, because the reference is towards a clustered index key and not to any physical location. However, if we drop the clustered index from the table, the logical lookup references based on the clustered index will become invalid, and hence the non-clustered indexes have to be reorganized.
Now, let’s see what will happen when we reorganize an existing clustered index. We know that a clustered index can be unique or non-unique. To ensure the uniqueness of non-unique clustered index keys, internally SQL Server automatically adds a uniquifier to every key of the clustered index (This does not happen with a unique clustered key). Now, if the non-unique clustered index is reorganized, the uniquifier is recreated, which will in turn make the lookup references invalid. So, the non-clustered indexes have to be recreated to ensure the integrity. But, if the clustered index is a unique clustered index, there is no uniquifier and therefore no uniquifier recreation is required during reorganization. Hence, the non-clustered indexes need not be recreated.
All the above things are the same in SQL Server 2005 except that the uniquifiers are not recreated for a non-unique clustered index during a index reorganization. Instead of recreating the uniquifiers, it will reuse the old uniquifiers. This means, even if the non-unique clustered index is recreated, there is no chance of the lookup references becoming invalid. So, there is no need of recreating the non-clustered indexes neither for a unique clustered index nor for a non-unique clustered index. Hence we can summarize as follows –
(Click on the image for a larger view)
Now, you can go back and see whether your index maintenance plans and scripts require any modification!