December 1, 2011 at 12:50 pm
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.
December 1, 2011 at 12:59 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2011 at 1:55 pm
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
December 1, 2011 at 2:05 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2011 at 3:30 pm
Great, thanks you guys for the prompt response.
December 1, 2011 at 3:55 pm
Ok, then I was basing my perspective on SQL 2000. Good to know.
CEWII
December 1, 2011 at 4:21 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2011 at 6:52 pm
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
December 2, 2011 at 1:16 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2011 at 7:55 am
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
December 2, 2011 at 8:13 am
---------------------------------------------------------------------
December 2, 2011 at 8:14 am
Elliott Whitlow (12/2/2011)
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.
In 2000:
Rebuilding a non-unique clustered index WILL rebuild the non-clustered indexes
Rebuilding a unique clustered index will NOT rebuild the non-clustered indexes
The first few service packs of 2000 had bugs that changed the behavior of rebuilding unique clustered indexes back and forth - this is the source of much of the confusion around this myth.
http://msdn.microsoft.com/en-us/library/cc966523.aspx
If you rebuild indexes for an entire table, you will need enough free space to build the clustered index and all nonclustered indexes. Similarly, if you rebuild a nonunique clustered index, you will also need free space for both the clustered and any nonclustered indexes. The nonclustered indexes are implicitly rebuilt because SQL Server must generate new unique identifiers for the rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2011 at 8:18 am
oh yes.... a point to me I do believe 😉
---------------------------------------------------------------------
December 2, 2011 at 8:21 am
george sibbald (12/2/2011)
oh yes.... a point to me I do believe 😉
1/4 of a point, since your post is 1/4 of mine. 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 2, 2011 at 8:25 am
1/2 each - final offer.
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply