January 14, 2009 at 8:53 am
hello, I have a quick question, I am creating a couple of new nonclustered indexes to a highly used table (Mostly reads, few writes). I know that when you create/change a clustered index you should rebuild the non clustered indexes, is it good practice to rebuild the clustered index when also creating non clustered indexes? or is not necessary?, i already have a maintance job that runs nightly that reorganizes/rebuilds according to % of fragmentation.
Thanks in advance for your comments.
January 14, 2009 at 9:12 am
JMO, but I would say this depends on how the non-clustereds you are creating are being used (meaning are they covering indexes or do you have to end up doing bookmark lookups). If the former is true, it won't be necessary at all since the structure of the table itself will not have impact on the covering index. If the latter is true, then it would not hurt to do the rebuild but still would not be completely necessary.
However, if you are rebuilding nightly based on fragmentation, I would say it is less likely that it will matter a great deal at all, since it will invariably get rebuilt soon enough anyway.
January 14, 2009 at 9:43 am
DBA (1/14/2009)
hello, I have a quick question, I am creating a couple of new nonclustered indexes to a highly used table (Mostly reads, few writes). I know that when you create/change a clustered index you should rebuild the non clustered indexes, is it good practice to rebuild the clustered index when also creating non clustered indexes? or is not necessary?, i already have a maintance job that runs nightly that reorganizes/rebuilds according to % of fragmentation.Thanks in advance for your comments.
When a table has a clustered index, the clustered index’s keys are part of the structure of the none clustered index. This means that when you create or modify the clustered index, SQL Server has to rebuild all the none clustered indexes to reflect the change of the clustered index. In that case you don’t have to rebuild the indexes, because you’ll be rebuilding indexes just after the server did it for you.
Creating a none clustered index on a table is not a reason to rebuild the clustered index. You rebuild the clustered index in order to fix fragmentation on the table. Creating the none clustered index has nothing to do with fragmentation.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 14, 2009 at 9:48 am
Thank you guys! 🙂
January 14, 2009 at 9:53 am
Just a note from BOL;
"Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified."
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
January 14, 2009 at 10:13 am
DavidB (1/14/2009)
Just a note from BOL;"Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified."
We were not talking about rebuilding the clustered index. We were talking about creating or modifying the clustered index. In those cases the none clustered indexes are being rebuild.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 14, 2009 at 10:17 am
Yup, just making sure that there wasn't confusion between the two. Thanks for the clarification.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply