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 ?
clustered-indexnonclustered-indexsql serversql server 2014sql-server-2008-r2
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 ?
Best Answer
From MSDN:
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).