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/
TopAnswers
Databases
Top Answer
Paul White
This is an efficient way to convert a nonclustered primary key to clustered, and rename it:
```sql
-- How the table looks now
CREATE TABLE dbo.Example
(
pk integer NOT NULL,
some_data integer NOT NULL,
CONSTRAINT PK_UnusualName
PRIMARY KEY NONCLUSTERED (pk)
);
-- Some data
INSERT dbo.Example (pk, some_data)
VALUES (1, 100), (2, 200), (3, 300);
-- Change the nonclustered PK to clustered
CREATE UNIQUE CLUSTERED INDEX PK_UnusualName
ON dbo.Example (pk)
WITH (DROP_EXISTING = ON);
-- Rename
EXECUTE sys.sp_rename
@objname = N'dbo.Example.PK_UnusualName',
@newname = N'PK__dbo_Example_pk',
@objtype = 'INDEX';
```