Search code examples
sql-servert-sql

How to make a Clustered PK Nonclustered on production DB?

I have a clustered PK on what is essentially a log table, which clearly shouldn't be clustered.

When I do:

alter table mytable drop constraint pk_mytable

It takes an absolute age on a copy of the DB on my machine (5+ mins) so will invariably not work on the production DB without causing timeouts or timing out itself.

Why isn't this an instant operation? What's it doing?

Is there any way to achieve this without taking my site offline?

Update: The table has 10s of millions of rows, and several 5? other nonclustered indexes.

Solution

  • Clustered indexes physically set the order of records on the disk with MS SQL Server. So this will result in a whole heap of IO as it rewrites the whole table.

    If you don't specify a clustered index, I believe SQL will create one for you anyway as it uses that to cross-reference indexes without necessarily reading records from the table.