what will happen if clustered index is dropped.

Question

what will happen if clustered index is dropped.

azad maratha

Default port

Points: 1468
More actions
July 13, 2010 at 1:53 am

#387336

hi,
one table .
2 indexes.(a-cluster,b-noncluster)
i fire a query name c which normally uses the b index cause it has created a execution plan which it reuses it if its in the cache.
now a is dropped and noncluster index is not rebuild.
so what will the optimiser do..
1.will it create a new execution plan and scan the whole table as it has become a heap.
2.use the same plan which uses b index.

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

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

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 1

If a clustered index is dropped, then the nonclustered index, which points to rows using the clustered key, will no longer be valid, and will need to be rebuilt. This will happen automatically, unless you disable the nonclustered index first (you'll still need to rebuild it before it can be used though).

So, in your scenario, the nonclustered index will be rebuilt automatically, and so will be available for use in your query.

azad maratha Default port Points: 1468 More actions · Answer 2

will the optimiser create a new execution plan or it will use the same one.

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 3

All execution plans for that table will be invalidated when the clustered index goes, so yes, a new plan will be generated.

azad maratha Default port Points: 1468 More actions · Answer 4

what if i again create the cluster index.what will happen then.

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

The same thing, in reverse.

The creation of the cluster will force a rebuild of all nonclustered indexes on the table. Any execution plans using that table will be invalidated.

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

lloyd-236007 SSC Enthusiast Points: 197 More actions · Answer 6

sql will generate a new plan since the index that was there isnt there anymore

=========

http://lloydom.blogspot.com/

azad maratha Default port Points: 1468 More actions · Answer 7

so execution plans which r there in cache are of no use when we rebuilt indexes.

then is it ok if i clear the cache after this process(rebuilding indexes).will it matter.

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

azadsale (7/13/2010)
so execution plans which r there in cache are of no use when we rebuilt indexes.

Execution plans that depended on those indexes.

then is it ok if i clear the cache after this process(rebuilding indexes).will it matter.

Why would you want to do that?

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

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 9

Can you explain what you are doing or thinking here? Is this an interview question of some sort?

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 10

azadsale (7/13/2010)
so execution plans which r there in cache are of no use when we rebuilt indexes.
then is it ok if i clear the cache after this process(rebuilding indexes).will it matter.

When dropping/building indexes execution plans that reference the base table will be invalidated meaning a new execution of one of such queries will trigger the recalculation of a brand new execution plan.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

azad maratha Default port Points: 1468 More actions · Answer 11

no ....

i just wanna clear my doubt.i have a interest in this section.thats all.

are my questions that stupid ?

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 12

The questions are not stupid, but it appears that you're asking about things without really doing much research, or you're making leaps that don't make sense. Why would you look at clearing the cache? It would help if you explained why you think things, or why you are asking the question rather than just randomly asking about things.

azad maratha Default port Points: 1468 More actions · Answer 13

yes ...it makes sense.i got to research first and if its still not clear i can post it.

thanks steve.