3

I have a table with a clustered PK. No foreign keys reference it. When I drop it I expect it to be more or less instantaneous, but it is instead very slow.

My assumption was that we'd just be removing some metadata and not having a constraint or clustering for future updates and inserts.

What is actually happening internally to make this expensive?

  • I admit the answer and the underlying issues are the same in this as in the referenced question. However, I somewhat object to the duplicate flag. The words primary key do not appear in the other question. The presence and importance of the nonclustered index is only revealed the discussion and answer to this one. I searched before posting and did not wind up with at the other answer. I think there is value in this as a standalone question even if the answer is the same. – Karl Kieninger May 30 '19 at 15:44
11

One of the reasons dropping the PK constraint, and the underlying clustered index, is expensive is that the underlying data is transformed into a HEAP. This affects all of the non-clustered indexes which have to update the pointers back to the row data from the old clustered index key to the new HEAP row. From Docs:

The pointer from an index row in a nonclustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.

So once you drop the PK and clustered index, the non-clustered indexes have their row locators updated from the clustered index key to the heap pointer. Depending on the size and number of indexes, there is potentially a substantial cost involved in this update.

This is also explained in this article:

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.

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