What happens when you delete a Clustered index and Non Clustered Index once created

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 ?

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

StackExchange Database Administrators Q#157317, answer score: 8