Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

drop clustered index ,rebuild or reorganize non clustered index? Expand / Collapse
Author
Message
Posted Tuesday, December 31, 2013 11:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 5:14 AM
Points: 48, Visits: 161
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?
Post #1526884
Posted Wednesday, January 1, 2014 12:34 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 36,983, Visits: 31,508
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526887
Posted Wednesday, January 1, 2014 1:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 5:14 AM
Points: 48, Visits: 161
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].
Post #1526890
Posted Wednesday, January 1, 2014 10:23 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 36,983, Visits: 31,508
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1526924
Posted Thursday, January 2, 2014 5:57 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 12:15 PM
Points: 245, 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
Post #1527068
Posted Thursday, January 2, 2014 6:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:16 AM
Points: 1,270, Visits: 2,784
I guess the question remains... why would you drop a clustered index on a table?


Post #1527089
Posted Thursday, January 2, 2014 7:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 36,983, Visits: 31,508
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1527107
Posted Thursday, January 2, 2014 10:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 5:14 AM
Points: 48, Visits: 161
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].
Post #1527220
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse