I have a SQL Server database, and have run sp_blitz 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?

The following script illustrates an efficient way to convert the existing nonclustered primary key to clustered, and to 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';

-- Tidy up
DROP TABLE dbo.Example;
  • 3
    Excellent, thanks. Can't beat a worked example! – hmallett Dec 20 '13 at 16:39

Your Answer

