Rebuilding A Clustered Index

Question

Post reply

Rebuilding A Clustered Index

TriggerMe

SSChampion

Points: 11752
More actions
January 31, 2006 at 10:23 pm

#170319

Does rebuilding a Clustered Index automatically rebuild all Non Clustered Indexes on a Table?
I'm talking about using DBCC REINDEX.
Kindest Regards,

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

Yes it does, and it is the most optimal way to recreate an index.

There's more info on the DBCC REINDEX command in BOL.

TriggerMe SSChampion Points: 11752 More actions · Answer 2

So then why didn't the 4 NON Clustered Indexes get rebuilt when I issued the command below on the Clustered Index?

DBCC DBREINDEX ('Act', PKActID, 90)

Kindest Regards,

Balmukund SSCertifiable Points: 6648 More actions · Answer 3

How did you check that NON clustered is not rebuilt?

TriggerMe SSChampion Points: 11752 More actions · Answer 4

Because when I did a DBCC SHOWCONTIG on the NON Clustered INDEX, the Fragmentation still exists!

Kindest Regards,

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

>>Does rebuilding a Clustered Index automatically rebuild all Non Clustered Indexes on a Table?

No.

It depends on whether the Clustered Index (CI) is unique or not.

If you want to be sure, execute this:

DBCC DBREINDEX ('Act', '', 90)

This will rebuild all indexes and will build the CI first, then the non-CI.

TriggerMe SSChampion Points: 11752 More actions · Answer 6

Thats fair enough PW but the Clustered Index in this case is Unique as it is the Primary Key!

I know your command will work but i thought that when you reindex a Clustered Index that is Unique, it will autoamtically reindex all NON Clustered Indexes.

Kindest Regards,

PW-201837 SSC-Insane Points: 20805 More actions · Answer 7

>>Thats fair enough PW but the Clustered Index in this case is Unique as it is the Primary Key

Sorry, I should have been clearer. When the Clustered Index is unique, then the non-clustered are not rebuilt.

So what you observed is to be expected.

The reason is that if the CI is unique, the keys needed for bookmark lookups in the other indexes will not change, therefore no need to touch them.

PW-201837 SSC-Insane Points: 20805 More actions · Answer 8

FYI: Here's a reference:

http://www.sql-server-performance.com/q&a131.asp

TriggerMe SSChampion Points: 11752 More actions · Answer 9

Aaahhhh... There is always something to learn. Thanks for clarifying that! It all makes sense.

Thanks.

Kindest Regards,