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