DBCC REINDEX on Clustered Index - effect on non-clustered
-
I have read on several SQL sites that performing DBCC REINDEX on a clustered index will cause all other indexes to be rebuilt also. However, I have found nothing to support this from Microsoft, and my initial tests show this to be untrue. Anyone out there have more knowledge on this aspect of DBCC REINDEX?
Thanks
Terry
-
If it is recreated or changed, then yes. I've never heard that about DBCC REINDEX.
-
-
First time I read that... I'll see what others have to say about this.
-
Before I get yelled at...DBCC DBREINDEX
-
We don't have many yellers here.. you should be ok
. -
I found another site http://weblogs.sqlteam.com/tarad/archive/2005/01/04/3933.aspx that says somthing a little different "It turns out that only when a non-unique clustered index gets rebuilt, that the non-clustered ones get rebuilt as well."
My tests have all been on unique clustered indexes.
-
yes but its seems to be embeded into its own script... hard to tell where this behavior comes from.
-
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Make sure you read closely through the http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx#EEAA chapter
Found it somewhere on the MS TechNet site
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
To sum up : yes it's true
. -
It has to be true ... an ordinary index is essentially a set of pointers to where the data can be found, a clustered index IS the data (i.e. the last point on the index tree is the data page itself) - this is why a clustered index is faster - because by the time the system has found the "pointer" to the data in the index it has found the data too - whereas with an ordinary index it still needs to go and retrieve the data itself.
Now when a clustered index is rebuit or rearranged in any way the data pages themselves are being moved about - so any other index that was pointing to those data pages needs to be reworked too - so though you may get away with not actually reorganising the other indexes they will be being reworked by the system as the data pages move around - so if you don't then subsequently reorganise all the other indexes after reorg of clustered index they will probably have become fragmented and inefficient
James Horsley
Workflow Consulting Limited -
No, it is NOT true!
The article I've mentioned and the link to Tara Duggan's blog mention NONUNIQUE CLUSTERED INDEXES. This is different from a UNIQUE CLUSTERED INDEX. Internally EVERY clustered index is "made" unique. If you didn't specify so, SQL Server automatically adds this 4 byte UNIQUIFIER, to ensure uniqueness. Now during a rebuild of a non-unique clustered index this value may change, thus all nonclustered indexes have to be rebuild as well. On the other hand, when the clustered index is created as unique, there is no UNIQUIFIER and during a rebuild the index keys do NOT change, thus no nonclustered index has to be rebuild here.
You can read this here: http://support.microsoft.com/default.aspx?scid=kb;en-us;304519
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanx for the correction
.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply