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

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.

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.

• Create a new table with the new structure

• 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

• 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

• 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.

• 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

StackExchange Database Administrators Q#91726, answer score: 11