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?
+1
A:
The clustered index is the data, that would explain the time it is taking to run.
Otávio Décio
2009-04-01 13:37:24
Can you expand please? Dropping the clustered index pk constraint does not drop the data itself (at least I'm in trouble if it does).Do you mean that the data is being reordered?
Mr. Flibble
2009-04-01 13:42:51
The clustered index determines how the data is physically arranged. When you drop it, the data will be rearranged - but not deleted, no worries there.
Otávio Décio
2009-04-01 13:48:08
Ok. So why would it bother rearranging it? If I don't specify an order why is it going to the bother of making up its own?
Mr. Flibble
2009-04-01 13:48:54
BTW this is why it is usually a waste to have the PK as the clustered index when sometimes you have better candidates (for example dates that might be used in range quereis).
Otávio Décio
2009-04-01 13:49:13
Actually that is a good question, what is the "default" data arrangement for tables that have no clustered indexes. That I don't know, I'll check my Internals book to see if I can find out.
Otávio Décio
2009-04-01 13:50:23
The default is to keep adding in order. SQL Server chooses to cluster the PK by default because 3 comes after 2 which comes after 1. When you INSERT a new row, it's easy to write it in order. If you cluster a UNIQUEID for example, you could be reformatting your hard drive on every insert :)
Timothy Khouri
2009-04-01 13:55:44
If the default is to keep adding it in the same order as when we have the clustered index it seems unlikely that it would bother reordering the table...Thanks for your comments all. I've canceled the operation now in hopes that there is a better way to change the indexes over remove old/add new
Mr. Flibble
2009-04-01 13:59:29
I'll post a new question about how to do this in a minute and add a link here.
Mr. Flibble
2009-04-01 14:00:00
I posted with more details of my problem if anyone would like to take a look:http://stackoverflow.com/questions/705790/best-way-to-change-clustered-index-pk-in-sql-2005
Mr. Flibble
2009-04-01 14:30:57
+6
A:
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.
Quassnoi
2009-04-01 13:49:43
A:
A "CLUSTERED" index will physically write the records of your table in order on the hard drive. So dropping or changing that index would likely cause SQL Server to basically 'defrag' (reorder) your hard drive (well, at least the part where the data for that table is).
Please note, this answer is not perfectly technical... but it's meant to give you the "oh, that's kinda what's happening" answer which is usually way more than good enough.
Timothy Khouri
2009-04-01 13:53:26