Cluster Re-Index

  • Hi Everyone,

    I just one want to ask, let say, if you re-index the cluster index in one table, it means all the non-clustered index also included?

    Your comment is highly appreciated.

    Thank you & regards

  • No.

    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
  • Thanks for the reply. Is there any article, or even in the BOL that proves if you do re-index the clustered index the non-clustered indexed is not included?

  • You could simply test it and prove it to yourself.

    It's stated here: http://msdn.microsoft.com/en-us/library/ms189858.aspx (table half way down titled "Rebuilding an Index") and here: http://msdn.microsoft.com/en-us/library/ms188388%28SQL.90%29.aspx (under the section REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]) and likely in several other places too.

    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
  • IF you drop and recreate a clustered index, nonclustered indexes are also recreated. This article explains it well: http://www.sql-server-performance.com/faq/rebuild_clustered_index_p1.aspx

    Typically, I do this: Script all indexes, drop all nonclustered indexes, drop clustered index, create clustered index, create nonclustered indexes....

    seems to work pretty fast.

  • NJ-DBA (9/3/2010)


    IF you drop and recreate a clustered index, nonclustered indexes are also recreated.

    If you drop and recreate a clustered index, all nonclustered indexes are recreated twice. Once when the cluster is dropped, once when it's recreated

    Typically, I do this: Script all indexes, drop all nonclustered indexes, drop clustered index, create clustered index, create nonclustered indexes....

    Fine if you're doing batch loads or data and don't want the overhead of the indexes during the load process. Utterly silly if all you're trying to do is defragment indexes.

    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
  • Hi Gail, you've posted help for me in the past, so I assume you are right and I am wrong, but let me make sure I understand what you are saying. If I recreate the clustered index, I'm recreating the nonclustered indexes twice... agreed not optimal. So instead I drop nonclustered first, then do the clustered, then the non clustered.... what is so silly about this? Are you saying better to just rebuild the clustered index? I have found through experience, especially when there are lots of large nonclustered indexes, that it actually goes faster if I do the method I explained.

  • If your goal is to rebuild indexes (remove fragmentation) just rebuild them. The rebuild has several algorithms it can use, including in most cases reading the old index to create the new one, something that isn't possible if you drop the index.

    Dropping and later recreating indexes is for when you want to bulk-load data and get it in fast without worrying about the overhead of the indexes.

    For removing fragmentation, use ALTER INDEX ... REBUILD or REORGANISE, depending on the fragmentation and size of the indexes. If you want to just rebuild everything - ALTER INDEX ALL ON <Table> REBUILD.

    If you drop then create there are downsides. Firstly you're possibly allowing duplicate data in while the unique indexes are not present. You're also increasing the logging impact. Finally if the definition of the index is changed and you forget to change your 'rebuild' script, those changes will be lost the next time the 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
  • Thanks guys for the link its very informative.

  • I was wondering about this too. If you have a clustered index on the table, the data is stored in the root level of the b-tree right? So when you re-index, you move the data around. That would make the non-clustered indexes invalid right? So it must update those indexes, just not rebuild them.

  • rivermorrison22 (10/28/2013)


    So when you re-index, you move the data around. That would make the non-clustered indexes invalid right?

    Nope. Nonclustered indexes don't contain the row's physical location when the table is a cluster.

    So it must update those indexes, just not rebuild them.

    Nope. Not touched in any way.

    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
  • So what does a non-clustered index actually do then, in the presence of a clustered index? If you had an employee table with a clustered index by employeeID, and a non-clustered index by lastname, would the nonclustered index contain a sorted list of last names, and associated employeeIDs, then reference the clustered index to return all the rows with those employeeids?

  • rivermorrison22 (10/28/2013)


    If you had an employee table with a clustered index by employeeID, and a non-clustered index by lastname, would the nonclustered index contain a sorted list of last names, and associated employeeIDs, then reference the clustered index to return all the rows with those employeeids?

    Pretty much, yes.

    http://www.sqlservercentral.com/articles/Indexing/68439/

    http://www.sqlservercentral.com/articles/Indexing/68563/

    http://www.sqlservercentral.com/articles/Indexing/68636/

    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
  • One option to consider when rebuilding indexes for defragmentation purposes is the WITH DROP_EXISTING option. You can read more about it at the following URL (2005 version)...

    http://technet.microsoft.com/en-us/library/ms188783(v=sql.90).aspx

    Once that page is up, do a page search for "DROP_EXISTING Clause" (without the quotes) for more information.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

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