Drop and create a clustered index

Question

Post reply

Drop and create a clustered index

paul

Valued Member

Points: 62
More actions
September 17, 2019 at 1:32 pm

#3680702

HI,
i'm trying to workout the impact of dropping an re creating a clustered index on the same key. I understand that if i drop a clustered index the table will be converted to a heap and any none clustered index will be rebuilt to include the RID. if this clustered index is then added again the none clustered indexes will be rebuilt again?
i'm a newbie and i have a little bit of an argument with DEV who do this every upgrade, other than performance issues are there any other implications?
thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 2

Two questions, what do you mean by upgrade? And why are they dropping and recreating instead of just rebuild?

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 3

I'll echo ZZartin's questions and ask one more... how big is this clustered index in rows, columns, and GB occupied?

--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)

Johan Bijnens SSC Guru Points: 135259 More actions · Answer 4

What's wrong with a simple ALTER INDEX cl_ix ON schema.table REBUILD ... ? ( as that does not impact the non-clustered indexes )

Keep in mind this will also interfere with ongoing operations on that table !

This reply was modified 4 years, 7 months ago by Johan Bijnens.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 5

Run a test, look at blocking with drop and create v rebuild. Add some activity on the table while you do this. If that doesn't help a developer understand, document their choice and be ready if this causes issues.

Is there a good reason from the dev to do this?

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 6

Looks like the OP has left the building.

--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)