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 7:55 am

    #1415903

    GilaMonster (12/2/2011)

    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.

    I think we are going to have to agree to disagree as far as SQL 2000 is concerned, I am 99.999% sure I can show that it does it even for unique clustered indexes in SQL 2000. Not prepared to take a solid position on SQL 2005 or above.

    I was always using DBCC DBREINDEX in SQL 2000, could that have been the difference?

    CEWII