Search code examples
sql-serversql-server-2005clustered-index

SQL Server 2005 Clustered Index Drop running long with no non-clustered indexes present

I'm dropping a clustered index from a table in a SQL Server 2005 database and it's taking a very long time to run.

I did some research and determined that dropping a clustered index can take a long time because it's updating pointers in non-clustered indexes to instead reference the RowID of the table itself, however in this particular scenario there are no non-clustered indexes present on the table.

There are a lot of foreign keys in the database, so it's possible that one of them might be referencing the clustered index ID.

Is there any way to determine what objects are using clustered index references as opposed to RowID?

Solution

  • If there's a clustered index, everything uses that instead of a RowID - the clustered index key IS the row identifier.

    So the answer is, anything that references that table.