CODEWITHSUNDEEP
sql-serverssms
Timotei Oros
Timotei Oros

Reputation: 107

How to create a clustered index in SQL Server while still preserving the primary key?

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.

Upvotes: 0

Views: 2677

Answers (1)

marc_s
marc_s

Reputation: 754508

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

Upvotes: 2

Related Questions