Home Forums SQL Server 2005 Administering Does rebuilding a clustered index effect non clustered index. RE: Does rebuilding a clustered index effect non clustered index.

  • December 2, 2011 at 1:16 am

    #1415740

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass