Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hawk
Starting Member
25 Posts |
 Posted - 2005-08-04 : 17:59:09
|
| I know when a clustered index is rebuilt that the non-clustered indexes are also rebuilt. But what about using DBCC INDEXDEFRAG on a clustered index? Does that cause non-clustered indexes to be defragmented also? |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-04 : 18:12:25
|
quote:
Originally posted by hawk
I know when a clustered index is rebuilt that the non-clustered indexes are also rebuilt. But what about using DBCC INDEXDEFRAG on a clustered index? Does that cause non-clustered indexes to be defragmented also?
Several things here. When a unique clustered index is rebuilt, the non-clustered indexes are not rebuilt (there's no need, as the clustering key has not changed). For non-unique clustered indexes, where we generate a uniquifier, the behavior changed several times during SQL Server 2000. The SP3a and SP4 behavior is correct - the uniquifiers are not regenerated during rebuild and so the non-clustered indexes are not rebuilt (again, because the clustering key which includes the uniquifier has not changed).INDEXEDFRAG only affects the index that was specified.ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
 |
|
|
hawk
Starting Member
25 Posts |
Posted - 2005-08-04 : 18:22:18
|
Thank you.Nothing like getting the answer from the man himself.(With "no warranties" of course  ) |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-08-04 : 18:36:55
|
quote:
Originally posted by hawk
Thank you.Nothing like getting the answer from the man himself.(With "no warranties" of course )
I hate having to put that there but as I'm brazenly identifying myself as an MS employee I have to otherwise the lawyers will get annoyed. I still hate it though.It was kind of embarrassing because RTM shipped with the correct behavior, then it broke in SP2 and we had to QFE it between SP2 and SP3...Paul RandalDev Lead, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
|
|
|
About DBCC INDEXDEFRAG on a clustered index?