sql-server add tag
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';

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.