Sql-server – Convert clustered primary key to non-clustered and use another column for the clustered index

clustered-indexdatabase-designindexsql serversql-server-2008

I have a large table with a primary key (GUID) that is also the clustered index. There is already a integer sequence based field. So I want to leave the GUID as the PK and make the integer column the clustered index.

I can't figure any way to do this except drop the original constraint and create new PK and and new clustered index. But this takes a long time and from what I gather, rebuilds the table twice, once to go from clustered index to heap, and then heap back to clustered index.

I can't do a table rebuild (create new, migrate data, swap names) since I can't have an outage.

Any ideas?

Version: SQL Server 2008 Service Pack 2, Developer/Enterprise.

Best Answer

I don't think you can perform this operation online, since you can't just move the clustered index from one column to another, and you can't use DROP_EXISTING to reduce the amount of work that has to be done.

However, you can avoid an outage, if you're willing to perform the work.

  1. Create a new table with the new structure
  2. Rename the original table, and create a view with the original name - the view will be a union of old and new table - performance won't be fantastic but it won't be offline
  3. Create INSTEAD OF triggers on the view to apply DML to old or new table
    • inserts should just go to new table
    • updates should try both
    • deletes should try both
  4. Copy rows, in chunks, from the old table to the new table. Will need SET IDENTITY_INSERT ON and you should perform each "chunk" in its own transaction.
  5. Once the old table contains no rows that don't exist in the new table, in a single transaction:
    • drop the old table
    • drop the view
    • rename the new table
Related Question