Post reply

Rebuilding A Clustered Index

  • 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,

  • January 31, 2006 at 10:36 pm

    #618044

    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.

  • January 31, 2006 at 10:49 pm

    #618045

    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,

  • February 1, 2006 at 3:24 am

    #618069

    How did you check that NON clustered is not rebuilt?

     

  • February 1, 2006 at 3:22 pm

    #618234

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

    Kindest Regards,

  • February 1, 2006 at 3:36 pm

    #618237

    >>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.

     

  • February 1, 2006 at 3:50 pm

    #618242

    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,

  • February 1, 2006 at 4:03 pm

    #618246

    >>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.

     

     

  • February 1, 2006 at 4:08 pm

    #618247

    FYI: Here's a reference:

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

     

  • February 1, 2006 at 4:31 pm

    #618250

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

    Thanks.

    Kindest Regards,

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

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