drop clustered index ,rebuild or reorganize non clustered index?

Question

drop clustered index ,rebuild or reorganize non clustered index?

sql crazy kid

Ten Centuries

Points: 1388
More actions
December 31, 2013 at 11:48 pm

#287772

If i drop a clustered index from table, how the non clustered behave when this is on same table.
Will all the non clustered index drop and recreate? or it just reorganize with rid?
What is the performance impact when droaping clustered index from a big table,also that having multiple non clustered index?

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

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

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 1

sql crazy kid (12/31/2013)
If i drop a clustered index from table, how the non clustered behave when this is on same table.
Will all the non clustered index drop and recreate? or it just reorganize with rid?
What is the performance impact when droaping clustered index from a big table,also that having multiple non clustered index?

If you drop a Clustered Index, all of the NCIs will rebuild because all NCIs contain either the Clustered Index Key or, in the absence of a CI, they will contain a rowID for the HEAP. So far as the performance impact goes, it depends. If a query is using the CI columns of the NCI and those suddenly go away, the index might not even be used for the query which might result in a table scan unless a different index is sufficient and is picked up by the query.

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

sql crazy kid Ten Centuries Points: 1388 More actions · Answer 2

Jeff Moden (1/1/2014)
sql crazy kid (12/31/2013)
If i drop a clustered index from table, how the non clustered behave when this is on same table.
Will all the non clustered index drop and recreate? or it just reorganize with rid?
What is the performance impact when droaping clustered index from a big table,also that having multiple non clustered index?
If you drop a Clustered Index, all of the NCIs will rebuild because all NCIs contain either the Clustered Index Key or, in the absence of a CI, they will contain a rowID for the HEAP. So far as the performance impact goes, it depends. If a query is using the CI columns of the NCI and those suddenly go away, the index might not even be used for the query which might result in a table scan unless a different index is sufficient and is picked up by the query.

Thanks Jeff,

This means all the non clustered for the table will be internally dropped and recreated either with new clustered key or rid [In case of no cix].

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

sql crazy kid (1/1/2014)
Jeff Moden (1/1/2014)
sql crazy kid (12/31/2013)
If i drop a clustered index from table, how the non clustered behave when this is on same table.
Will all the non clustered index drop and recreate? or it just reorganize with rid?
What is the performance impact when droaping clustered index from a big table,also that having multiple non clustered index?
If you drop a Clustered Index, all of the NCIs will rebuild because all NCIs contain either the Clustered Index Key or, in the absence of a CI, they will contain a rowID for the HEAP. So far as the performance impact goes, it depends. If a query is using the CI columns of the NCI and those suddenly go away, the index might not even be used for the query which might result in a table scan unless a different index is sufficient and is picked up by the query.
Thanks Jeff,
This means all the non clustered for the table will be internally dropped and recreated either with new clustered key or rid [In case of no cix].

I don't believe they're actually dropped, just rebuilt. That's kind of expensive because the pages of the old version of the index aren't actually dropped until the new version is available and that could cause a bit of growth in the file group. Also, unless you're in the Bulk-Inserted or Simple recovery mode, the rebuilds will all be fully logged.

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

Veteran Jones SSC Eights! Points: 937 More actions · Answer 4

This is just one article you can look at about dropping a clustered index.

http://technet.microsoft.com/en-US/library/ms190691(v=SQL.105).aspx

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 5

I guess the question remains... why would you drop a clustered index on a table?

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

Markus (1/2/2014)
I guess the question remains... why would you drop a clustered index on a table?

I can see doing it only if you need to change the keys for something like partitioning but, even then, I believe a REBUILD will do it. Other than that, I can't see doing it.

Point well taken though.

@SQL Crazy Kid,

Why do you need to drop the Clustered Index?

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

sql crazy kid Ten Centuries Points: 1388 More actions · Answer 7

Jeff Moden (1/2/2014)
Markus (1/2/2014)
I guess the question remains... why would you drop a clustered index on a table?
I can see doing it only if you need to change the keys for something like partitioning but, even then, I believe a REBUILD will do it. Other than that, I can't see doing it.
Point well taken though.
@SQL Crazy Kid,
Why do you need to drop the Clustered Index?

Hello,

My post is to measure the impact in case we drop and recreate CIX either with new combosit key or change of key.I was in assumption that all the NIX associated with the CIX will reorganize instead of rebuild [Reorganizing an index uses minimal system resources].