Is there a way to convert a non-clustered index on a Primary Key to a clustered one? (SQL Server 2008)

Question

With Management Studio I can't alter the index. Dropping it doesn't work because it's being used as a foreign key in a lot of other tables. Can I somehow alter it? Or how would you do this?

Answer 1

If your existing PK is referenced by many other tables then you're going to spend many tedious and error-prone minutes writing the script to drop all the FK references and recreate them.

SQL Server Management Studio can do this for you. What you may not have realized is that you can only have one clustered index on a table, because the clustered index represents the physical ordering of rows; this means that you first have to bring up the clustered index and turn off clustering. Then and only then can you pull up another index and turn on clustering.

You need to do this from the table designer, then right-click and choose Indexes/Keys.... First find the existing clustered index (probably the primary key) and change Create as Clustered to No. Then go to the other index and change Create as Clustered to Yes for that. If the table is large, the operation is liable to time out while you save; you can get around this by having SSMS generate a change script (right-click on the designer after changing the indexes and you'll see the option). Then you can run this script in a query window with no timeout.

If you look at this change script, you'll see all of the work it's doing creating staging tables and switching keys around; it's a pain to write this manually. Let SSMS do it for you.

Answer 2

You can't convert it in-place - you need to drop the primary key constraint first (which will also automatically drop the nonclustered index "behind" the primary key constraint), and then re-create it as a clustered index:

ALTER TABLE dbo.YourTable
DROP CONSTRAINT PK_YourTable

and then re-create it as clustered:

ALTER TABLE dbo.YourTable
ADD CONSTRAINT PK_YourTable
PRIMARY KEY CLUSTERED (YourPKField)