hmallett imported from SE
I have a SQL Server database, and have run [`sp_blitz`][1] against it. This pulled out that there are a couple of heaps on moderate size tables (a few hundred thousand rows in one case).

Most of these tables have a primary key (one does not, but I'll deal with that later). The primary key itself has an unusual name - i.e. not the default one, when most of the tables have a default primary key name (`PK_tablename`).

There is an index with a name matching the primary key, which is unique and non-clustered.

I can rename the primary key, but I then think I should be creating a clustered index. If I do this, then I'll have duplicate indexes, so it would make sense to remove the non-clustered index. However, it's being used for the primary key.

If I move the primary key constraint from the old non-clustered index to the new clustered index:

1. Does this make sense?
2. Are there any things I should be aware of?
3. What is the best method to do this?

  [1]: http://www.brentozar.com/blitz/
Top Answer
Paul White
This is an efficient way to convert a nonclustered primary key to clustered, and rename it:

-- How the table looks now
CREATE TABLE dbo.Example
    pk integer NOT NULL,
    some_data integer NOT NULL,
    CONSTRAINT PK_UnusualName
-- Some data
INSERT dbo.Example (pk, some_data)
VALUES (1, 100), (2, 200), (3, 300);
-- Change the nonclustered PK to clustered
ON dbo.Example (pk)
-- Rename
EXECUTE sys.sp_rename 
    @objname = N'dbo.Example.PK_UnusualName',
    @newname = N'PK__dbo_Example_pk',
    @objtype = 'INDEX';
Moving primary key constraint from one index to another

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.