• 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].