What happens to non-clustered indexes when the table structure is changed?

re: What happens to non-clustered indexes when the table structure is changed?

Joe Ferry — Wed, 02 Jan 2008 19:38:12 GMT

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.

.NET Tips & Tricks

Michael's Coding Den — Wed, 13 Jun 2007 22:13:31 GMT

Last update: June 13 , 2007 Document version 0.6 Preface If you have something to add, or want to take

.NET Tips & Tricks

Michael's Coding Den — Wed, 13 Jun 2007 22:13:30 GMT

Last update: June 13 , 2007 Document version 0.6 Preface If you have something to add, or want to take

re: What happens to non-clustered indexes when the table structure is changed?

Paul Randal - MSFT — Wed, 13 Jun 2007 21:16:23 GMT

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.

Thanks

re: What happens to non-clustered indexes when the table structure is changed?

KS. — Wed, 13 Jun 2007 14:10:42 GMT

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 .

re: What happens to non-clustered indexes when the table structure is changed?

Paul Randal - MSFT — Tue, 12 Jun 2007 01:31:28 GMT

Hi Leigh,

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.

Thanks

re: What happens to non-clustered indexes when the table structure is changed?

parikht — Mon, 11 Jun 2007 19:59:23 GMT

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.

Tejas

re: What happens to non-clustered indexes when the table structure is changed?

lverhave — Fri, 08 Jun 2007 11:27:03 GMT

Hi 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?

Thanks

Leigh

re: What happens to non-clustered indexes when the table structure is changed?

Arun Philip — Fri, 08 Jun 2007 11:27:00 GMT

Quite informative. Thank you, Paul