Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Does rebuilding a clustered index effect non clustered index. Expand / Collapse
Author
Message
Posted Thursday, December 1, 2011 12:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 10, 2014 3:22 PM
Points: 74, Visits: 475
Hello All, I have an interesting question asked by a freind.

we have a table with 1 clustered and 2 non clustered indexes.
lets assume the clustered index is fragmented and if we rebuild or reorg the clustered index, all the data is going to get rearranged which means the pointers pointing to the data will also get rearranged(I assume). if the pointers pointing to the data gets rearranged, will it fragment the non clustered indexes.

this question is confusing me on my understanding on indexes. Can some one help me on answering this.

thanks in advance.
Post #1214927
Posted Thursday, December 1, 2011 12:59 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
It'll have absolutely no effect on the nonclustered indexes. Nonclustered indexes use the clustered index key as a 'pointer' and that doesn't change in a rebuild.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1214932
Posted Thursday, December 1, 2011 1:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
Gila is that correct. If the record moves at all doesn't the pointer change as well? It was my understanding that this was true and the underlying cause that when a DBCC DBREINDEX was performed that all non-clustered indexes HAD to be redone because the pointers changed? Is this inaccurate?

CEWII
Post #1214980
Posted Thursday, December 1, 2011 2:05 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
Elliott Whitlow (12/1/2011)
Gila is that correct. If the record moves at all doesn't the pointer change as well?


If a table has a clustered index, the 'pointer' is the key value for the clustered index. That certainly doesn't change when an index is rebuilt (if I have a cluster on an identity column, a rebuild doesn't cause the row with an ID of 50 to suddenly become 12)

It was my understanding that this was true and the underlying cause that when a DBCC DBREINDEX was performed that all non-clustered indexes HAD to be redone because the pointers changed? Is this inaccurate?


Nonclustered indexes aren't redone when the clustered index is rebuild. Rebuilding a clustered index just rebuilds the cluster, the nonclustered indexes aren't touched (on SQL 2000 if the cluster wasn't unique then nonclustered indexes would be rebuild when the clustered index was, that was fixed in SQL 2005)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1214987
Posted Thursday, December 1, 2011 3:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 10, 2014 3:22 PM
Points: 74, Visits: 475
Great, thanks you guys for the prompt response.
Post #1215045
Posted Thursday, December 1, 2011 3:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
Ok, then I was basing my perspective on SQL 2000. Good to know.

CEWII
Post #1215052
Posted Thursday, December 1, 2011 4:21 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
Elliott Whitlow (12/1/2011)
Ok, then I was basing my perspective on SQL 2000. Good to know.


Even on SQL 2000 the 'pointers' in a nonclustered index are the clustered index key and they don't change when the clustered index is rebuilt.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1215063
Posted Thursday, December 1, 2011 6:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
GilaMonster (12/1/2011)
Elliott Whitlow (12/1/2011)
Ok, then I was basing my perspective on SQL 2000. Good to know.


Even on SQL 2000 the 'pointers' in a nonclustered index are the clustered index key and they don't change when the clustered index is rebuilt.
But the non-clustered indexes were rebuilt when the primary key was reindexed.

CEWII
Post #1215090
Posted Friday, December 2, 2011 1:16 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
Elliott Whitlow (12/1/2011)
GilaMonster (12/1/2011)
Elliott Whitlow (12/1/2011)
Ok, then I was basing my perspective on SQL 2000. Good to know.


Even on SQL 2000 the 'pointers' in a nonclustered index are the clustered index key and they don't change when the clustered index is rebuilt.
But the non-clustered indexes were rebuilt when the primary key was reindexed.


No, they weren't. The nonclustered indexes would only be automatically rebuilt when a non-unique clustered index was rebuild (because the uniqueifiers weren't kept the same). If the clustered index was unique (as in the case of a primary key) then the nonclustered indexes wouldn't be touched.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1215187
Posted Friday, December 2, 2011 7:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
GilaMonster (12/2/2011)
Elliott Whitlow (12/1/2011)
GilaMonster (12/1/2011)
Elliott Whitlow (12/1/2011)
Ok, then I was basing my perspective on SQL 2000. Good to know.


Even on SQL 2000 the 'pointers' in a nonclustered index are the clustered index key and they don't change when the clustered index is rebuilt.
But the non-clustered indexes were rebuilt when the primary key was reindexed.


No, they weren't. The nonclustered indexes would only be automatically rebuilt when a non-unique clustered index was rebuild (because the uniqueifiers weren't kept the same). If the clustered index was unique (as in the case of a primary key) then the nonclustered indexes wouldn't be touched.
I think we are going to have to agree to disagree as far as SQL 2000 is concerned, I am 99.999% sure I can show that it does it even for unique clustered indexes in SQL 2000. Not prepared to take a solid position on SQL 2005 or above.

I was always using DBCC DBREINDEX in SQL 2000, could that have been the difference?

CEWII
Post #1215370
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse