Why can it take so long to drop a clustered index?

Generally speaking, indexes can speed up queries tremendously. This comes at the cost, as changes to the underlying data have to be reflected in the indexes when the index column(s) are modified.

Before we get into the reasons why dropping a clustered index can be time-consuming, we need to take a short look at the different index structures in SQL Server.

Every table can have one, and only one clustered index. A clustered index sorts the data physically according to its index key. And since there can only be one physically sort order on a table at a time, this sounds pretty obvious. If a table does not have a clustered index, it is called a heap.

The second index structure is called a non-clustered index. You can create non-clustered indexes on tables with clustered indexes, heaps, and indexed views.

The difference between both index structures is at the leaf level of the index. While the leaf level of a clustered index actually is the table’s data itself, you only find pointers to the data at the leaf level of a non-clustered index. Now we need to understand an important difference:

  • When a table has a clustered index created, the pointers contain the clustered index keys for that row. 
  • When a table does not have a clustered index, the pointers consist of the so-called RowID, which is a combination of FileNumber:PageNumber:Slot.

When you understand this distinction, you can derive the answer to the original question yourself. When you drop a clustered index, SQL Server will have to recreate all non-clustered indexes on that table (assuming there are any). During this recreation, the clustered index keys are replaced by the RowID. It This is a time-consuming operation, especially on larger tables or tables with many indexes.


One Response to “Why can it take so long to drop a clustered index?”

  1. Thanks for this useful piece of information.
    Unfortunately for us, we had already started the Drop Index Query for the Clustered Index on a table that was 200+ GBs and had 3 Non Clustered Indexes on it.
    The query has been running forever now, waiting seems to be the only option.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |