2

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?

  • 4
    Because you told SQL Server to create a separate index, and it did what you told it to. You can create 50 identical indexes if you want, too; SQL Server will create and maintain them all. – Aaron Bertrand Aug 4 '15 at 11:30
  • 4
    The question is: why did you add the unique index if you already have a PK on that column? – a_horse_with_no_name Aug 4 '15 at 11:50
  • 1
    If you want to keep one (CI) index, you can drop the JobItemsIndex and convert the PK to use a clustered index. – ypercubeᵀᴹ Aug 4 '15 at 12:18
  • 1
    @ypercube azure does not support heaps apparently: social.msdn.microsoft.com/Forums/azure/en-US/… – JNK Aug 4 '15 at 12:55
  • 1
    How about dropping the primary key and just keep the unique constraint? I'm not able to test but I think the unique constraint is enough to allow foreign key constraints against the table. – Mikael Eriksson Aug 4 '15 at 12:55
4

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.

|improve this answer|||||
6

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)
);
|improve this 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.

|improve this answer|||||
  • Right - the PK is only providing something that is flagged as being the PK. It offers no actual benefit, because all its functionality is provided by the CIX. – Rob Farley Aug 4 '15 at 22:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.