I've made mistake creating clustered primary key on GUID column. There are many tables that reference that table with defined 开发者_StackOverflow中文版foreign keys. Table size is not significant.
I would like to convert it from clustered to non-clustered without manually dropping and recreating any foreign keys or even primary key constraint.
Is it possible to achieve that in MS SQL2005 and how if yes ?
Is it possible to achieve that ONLINE (without db down time) if yes ?
You could try creating the unique nonclustered NC index first, then drop the clustered PK. The FK should recognise this other index (but might not: never tried it).
When you run ALTER TABLE to drop the clustered PK use the ONLINE option. However, it's only available in Enterprise edition.
ALTER TABLE Mytable DROP CONSTRAINT PK_Mytable WITH (ONLINE = ON)
You can't use ONLINE for the ADD CONSTRAINT bit.
Basically, your options are limited without blocking, or creating another table first and moving data over...
精彩评论