I have a table named Players with these columns

Id, Name, Age

Id and Age are ints, and Name is a char(50).

I want to create a clustered index on this table, so that the table will be sorted in ascending order by the Name. I have found out that every table with a primary key already has a clustered index on the primary key, and that there can only be one clustered index on a table, and if I want to add another one, I have to drop the primary key constraint that's on the Id.

My Player.Id is already as a foreign key in multiple other tables, so I still want to have the primary key constraint on the Id, but I also want a clustered index to sort the records by the name.

How can I do that in SQL Server? I am using Microsoft SQL Server Management Studio.

CodePudding user response：

You need to:

1. First remove all FK constraints to your table Player
2. Then drop the primary clustered index on Id
3. Create a new primary key with a non-clustered index on Player.Id - use this command: ALTER TABLE dbo.Player ADD CONSTRAINT PK_Player PRIMARY KEY NONCLUSTERED (Id);
4. Then create a clustered index on Player.Name
5. Re-establish all FK constraints from all your table, to Player