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?
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,
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);
@objname = N'dbo.Example.PK_UnusualName',
@newname = N'PK__dbo_Example_pk',
@objtype = 'INDEX';