Cluster Re-Index

Question

Cluster Re-Index

andriancruz

SSC Veteran

Points: 287
More actions
September 3, 2010 at 2:18 am

#237783

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

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

andriancruz SSC Veteran Points: 287 More actions · Answer 2

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

NJ-DBA SSChampion Points: 13834 More actions · Answer 4

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

NJ-DBA SSChampion Points: 13834 More actions · Answer 6

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

andriancruz SSC Veteran Points: 287 More actions · Answer 8

andriancruz

SSC Veteran

Points: 287

September 5, 2010 at 6:57 pm

#1216735

Thanks guys for the link its very informative.

rivermorrison22 Valued Member Points: 50 More actions · Answer 9

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

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

rivermorrison22 Valued Member Points: 50 More actions · Answer 11

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

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)