SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


drop clustered index ,rebuild or reorganize non clustered index?


drop clustered index ,rebuild or reorganize non clustered index?

Author
Message
sql crazy kid
sql crazy kid
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 243
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211361 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sql crazy kid
sql crazy kid
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 243
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
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211361 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Veteran Jones
Veteran Jones
Old Hand
Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)Old Hand (323 reputation)

Group: General Forum Members
Points: 323 Visits: 348
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
Summer90
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7041 Visits: 3831
I guess the question remains... why would you drop a clustered index on a table?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211361 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sql crazy kid
sql crazy kid
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 243
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].
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search