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

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)

Answer 2

You'll have to also drop the FK constraints, change the PK and then recreate the FK constraints after the new PK is in place. Foreign key constraints require the referenced key to be unique, a primary key is, by definition, unique, so dropping the PK is not allowed while FK constraints are in place referencing the PK.

Changing to a clustered index rewrites the table. Any time I consider moving to/from a clustered index or changing the clustered index, I would reconsider its necessity and choice of the keys to the clustered index - particularly if it's not going to be unique or increasing (like a datetime for a timestamp). Remember, a clustered index is not really an index in the first place - it is the order of data in the pages. This is why clustering on an increasing key helps with page splits when you add data to the table.

Answer 3

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.