Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What happens when I drop a clustered primary key in SQL 2005

I've a PK constraint - a clustered index on two columns - which I am in the process of dropping. The command is still running after an hour. I would have thought that as I am just removing a constraint the operation would be nearly instantaneous. Can someone explain to me what is actually happening under the hood when I drop the PK?

like image 744
Mr. Flibble Avatar asked Dec 30 '22 02:12

Mr. Flibble


2 Answers

Clustered index is not "just a constraint", it's a storage method.

When you drop it, your data are being reordered from clustered storage to heap storage

Other indexes are being updated to refer to RID's instead of PRIMARY KEY values.

like image 163
Quassnoi Avatar answered Jan 14 '23 12:01

Quassnoi


The clustered index is the data, that would explain the time it is taking to run.

like image 38
Otávio Décio Avatar answered Jan 14 '23 12:01

Otávio Décio