Here’s a topic that’s cropped up several times during Q&As at TechEd this year – what happens to non-clustered indexes when changes are made to the underlying table? Are they always rebuilt or not?
Before we get into that discussion, I’ll give you a little background.
One way to describe a non-clustered index is whether it is a covering index or not. A covering index is one which has all the table columns necessary to satisfy a query, and so there is no need to go back to the underlying table (which I’ll call the base table) to fetch additional columns. Most non-clustered indexes are not covering indexes and so it is necessary for each non-clustered index record to include a link back to the corresponding base table record. This link takes various forms, depending on the structure of the base table:
Ok - background out of the way. Now let's discuss what happens to non-clustered indexes when various actions are performed to the base table.
SQL Server 2000
SQL Server 2005
So the rule of thumb to remember is: if the physical or logical RID contained in the non-clustered index records needs to change then all the non-clustered indexes need to be rebuilt. Its as simple as that.
Hopefully this will help to clear up some confusion - let me know of any questions!
Quite informative. Thank you, Paul
You mentioned in "changing the clustered index schema - changing anything esle" in SQL Server 2005 that if you move the clustered index to a different filegroup one would not have to rebuild the non-clustered indexes. What if the filegroup is in another drive, wouldn't the base table also be moved thus changing the physical RID?
This is a very helpful article. Your rule of thumb says it all. If we start thinking of rebuilding indexes in that manner, it's pretty straight forward then. Thank you, Pal, for this helpful information.
Moving a clustered index has no effect on non-clustered indexes as the RID that is included in the non-clustered index is purely logical - it has nothing to do with the physical location of the clustered index.
We have a heap table with non-clustered indexes on it ,If we partition it and move the half the data & related index from current filegroup to other filegroup which is on other drive will it affect the non-clustered index ie will they be rebuilt .
In 2000 does the non-clustered index (on Heap )needs a rebuilt when we move it to other file group on other disk .
KS - yes - ANY time the physical or logical RID changes, non-clustered indexes have to be rebuilt. In your example, the physical RID of the heap rows will change when they're moved to the new filegroups.
Last update: June 13 , 2007 Document version 0.6 Preface If you have something to add, or want to take
Moving a unique clustered index to a different filegroup, or anywhere else, doesn't affect the nonclustered indexes because the leaf level of the nonclsutered index references sthe clustering key of the clustered index, not a RID or logical RID. The engine works through the B-Tree of the clustered index using the clustering key. A search using a non-clustered index where there is a clsutered index, works through two B-Trees to get to the full data row: one search through the B-Tree for the nonclustered index, and one for the clustered index.
Paul Your articles are always full of knowledge and really gives good insight into SQL server Storage engine and query processing .Thanks for contributing
this is very informative. I have gone through most of your articles and training courses. You have provided the best information. Thanks for the myths course. It cleared out many of my questions in mind. And this information is also very useful.