Does rebuilding a clustered index effect non clustered index.

  • striker-baba

    SSCrazy

    Points: 2169

    Hello All, I have an interesting question asked by a freind.

    we have a table with 1 clustered and 2 non clustered indexes.

    lets assume the clustered index is fragmented and if we rebuild or reorg the clustered index, all the data is going to get rearranged which means the pointers pointing to the data will also get rearranged(I assume). if the pointers pointing to the data gets rearranged, will it fragment the non clustered indexes.

    this question is confusing me on my understanding on indexes. Can some one help me on answering this.

    thanks in advance.

  • Gail Shaw

    SSC Guru

    Points: 1004424

    It'll have absolutely no effect on the nonclustered indexes. Nonclustered indexes use the clustered index key as a 'pointer' and that doesn't change in a rebuild.

    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
  • Elliott Whitlow

    SSC Guru

    Points: 102296

    Gila is that correct. If the record moves at all doesn't the pointer change as well? It was my understanding that this was true and the underlying cause that when a DBCC DBREINDEX was performed that all non-clustered indexes HAD to be redone because the pointers changed? Is this inaccurate?

    CEWII

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Elliott Whitlow (12/1/2011)


    Gila is that correct. If the record moves at all doesn't the pointer change as well?

    If a table has a clustered index, the 'pointer' is the key value for the clustered index. That certainly doesn't change when an index is rebuilt (if I have a cluster on an identity column, a rebuild doesn't cause the row with an ID of 50 to suddenly become 12)

    It was my understanding that this was true and the underlying cause that when a DBCC DBREINDEX was performed that all non-clustered indexes HAD to be redone because the pointers changed? Is this inaccurate?

    Nonclustered indexes aren't redone when the clustered index is rebuild. Rebuilding a clustered index just rebuilds the cluster, the nonclustered indexes aren't touched (on SQL 2000 if the cluster wasn't unique then nonclustered indexes would be rebuild when the clustered index was, that was fixed in SQL 2005)

    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
  • striker-baba

    SSCrazy

    Points: 2169

    Great, thanks you guys for the prompt response.

  • Elliott Whitlow

    SSC Guru

    Points: 102296

    Ok, then I was basing my perspective on SQL 2000. Good to know.

    CEWII

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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.

    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
  • Elliott Whitlow

    SSC Guru

    Points: 102296

    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.

    CEWII

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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
  • Elliott Whitlow

    SSC Guru

    Points: 102296

    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

  • george sibbald

    SSC Guru

    Points: 104200

    http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-19-Misconceptions-around-index-rebuilds-(allocation-BULK_LOGGED-mode-locking).aspx

    ---------------------------------------------------------------------

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Elliott Whitlow (12/2/2011)


    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.

    http://sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-19-Misconceptions-around-index-rebuilds-(allocation-BULK_LOGGED-mode-locking).aspx

    In 2000:

    Rebuilding a non-unique clustered index WILL rebuild the non-clustered indexes

    Rebuilding a unique clustered index will NOT rebuild the non-clustered indexes

    The first few service packs of 2000 had bugs that changed the behavior of rebuilding unique clustered indexes back and forth - this is the source of much of the confusion around this myth.

    http://msdn.microsoft.com/en-us/library/cc966523.aspx

    If you rebuild indexes for an entire table, you will need enough free space to build the clustered index and all nonclustered indexes. Similarly, if you rebuild a nonunique clustered index, you will also need free space for both the clustered and any nonclustered indexes. The nonclustered indexes are implicitly rebuilt because SQL Server must generate new unique identifiers for the rows.

    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
  • george sibbald

    SSC Guru

    Points: 104200

    oh yes.... a point to me I do believe 😉

    ---------------------------------------------------------------------

  • Gail Shaw

    SSC Guru

    Points: 1004424

    george sibbald (12/2/2011)


    oh yes.... a point to me I do believe 😉

    1/4 of a point, since your post is 1/4 of mine. 😉

    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
  • george sibbald

    SSC Guru

    Points: 104200

    1/2 each - final offer.

    ---------------------------------------------------------------------

Viewing 15 posts - 1 through 15 (of 18 total)

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