Why does PK constraint require a separate index when I already have a suitable unique clustered index?

Question

This is part of table definition from here:

CREATE TABLE [dbo].[JobItems] (
[ItemId]            UNIQUEIDENTIFIER NOT NULL,
-- lots of other columns
CONSTRAINT [PrimaryKey_GUID_HERE] PRIMARY KEY NONCLUSTERED ([ItemId] ASC)
);

CREATE UNIQUE CLUSTERED INDEX [JobItemsIndex]
ON [dbo].[JobItems]([ItemId] ASC);

This is some legacy design, so please don't ask "why". Anyway when I look up the list of indexes I see that there're two indexes - one of them is JobItemsIndex and the other is PK-GUID-HERE and they are both for JobItems table.

My question is...

Why is there a need for a separate index to maintan the PK when I already have JobItemsIndex which is unique and includes the very same column and so suitable for maintaining the PK constraint?

Answer 1

The other two answers are spot on in that the reason why the two indexes exist: you told the database to create two indexes. Furthermore you could make your primary key clustered and remove the second index.

To answer your question about why the second index is "needed" boils down to a limitation/requirement for Sql Server Azure edition databases that isn't present in other versions of Sql Server. Sql Server Enterprise/Standard/Express all support heap tables. Sql Server Azure edition doesn't support heap tables. My guess is at some point they wanted the "JobItems" table to be a heap table. When the time came to put the database in the cloud they were forced to have a clustered index on the table. The developer chose to create a duplicate index that is clustered instead of changing the primary key from nonclustered to clustered. Why they chose this we may never know... However this seems like a plausible path to your situation, especially if this "legacy" table lived outside of Azure at some point in its life.

It might also be beneficial to review this question and this question on StackOverflow where they discuss nonclustered and clustered indexes on the same column. The top rated answer in the first question states that there might be a performance boost to having both indexes. The answers for the second question basically reiterate in more detail what others have said already in response to your question.

Answer 2

It doesn't need a separate index. It's only done that way because you've actually told it to be in the script. You could do this with the same functionality:

CREATE TABLE [dbo].[JobItems] (
[ItemId]            UNIQUEIDENTIFIER NOT NULL,
-- lots of other columns
CONSTRAINT [JobItemsIndex] PRIMARY KEY CLUSTERED ([ItemId] ASC)
);

Answer 3

In your particular case it doesn't really matter since both your unique clustered index and your primary key are on the same column(s) but there are two minor differences between the two.

• A primary key requires that all columns be non-nullable.

• A unique key allows NULL values.

• You can have only one primary key.

• You can have multiple unique keys.

Either one can be clustered. And you can have only one clustered key.

In your case however since both your primary key and your index are identical you can easily drop both and re-create your primary key as a clustered index. That being said if it's a large table I'm not sure I would bother. I would probably just drop the primary key (no reason to maintain it) and live with the clustered unique index. In case you are worried that does NOT make it a HEAP. You have a clustered index so you do not have a heap.