1

What happens when we dropped a Cluster index and Non Clustered Index once they are created?

What will happen to the data at the leaf level according to Clustered Index ?

6

From MSDN:

When a clustered index is dropped, the data rows that were stored in the leaf level of the clustered index are stored in an unordered table (heap). Dropping a clustered index can take time because in addition to dropping the clustered index, all nonclustered indexes on the table must be rebuilt to replace the clustered index keys with row pointers to the heap. When you drop all indexes on a table, drop the nonclustered indexes first and the clustered index last. That way, no indexes have to be rebuilt.

https://technet.microsoft.com/en-us/library/ms190691(v=sql.105).aspx

As for the nonclustered index drops, they aren't DELETING any data, it's a metadata operation and the index pages are simply deallocated. Think about TRUNCATE versus DELETE, TRUNCATE just deallocates the pages and you're done. Dropping a nonclustered index is very similar, hence why dropping one is lightning fast when you do it (unless you are blocked).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.