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