4

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?

Mr. Flibble
  • 26,564
  • 23
  • 69
  • 100

3 Answers3

9

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
  • 413,100
  • 91
  • 616
  • 614
1

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

Otávio Décio
  • 73,752
  • 17
  • 161
  • 228
  • 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 Apr 01 '09 at 13:42
  • 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 Apr 01 '09 at 13:48
  • 1
    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 Apr 01 '09 at 13:48
  • 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 Apr 01 '09 at 13:49
  • 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 Apr 01 '09 at 13:50
  • 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 Apr 01 '09 at 13:55
  • 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 Apr 01 '09 at 13:59
  • I'll post a new question about how to do this in a minute and add a link here. – Mr. Flibble Apr 01 '09 at 14: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 Apr 01 '09 at 14:30
0

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
  • 31,315
  • 21
  • 88
  • 128