Non clustered index on the same column as the clustered primary key should be unique?

Question

In a large People's table which has a PersonID column as primary key (int identity) and is clustered indexed by that (PK_People), with a whole bunch of additional columns, I had the need to add a non clustered index also on the same column with only one additional included column (Name).

The reason for that would apparently be because this table is most of the time inner joined with other tables via it's primary key (PersonID) just to get the Person Name and not all the other columns. It seems that SQL Server would prefer to use this index because it's much smaller than the whole clustered index (actual entire table).

I saw that need because SQL Server was actually using another non clustered index that existed in the table for the Name column alone, to retrieve the Name for the joins and thus having to scan the whole index to find the matching PersonID for the person it was looking for. Still it was preferring this over doing a key lookup using the actual clustered index. (Maybe because of the size of it?)

Then, the moment I created an index like this:

CREATE UNIQUE NONCLUSTERED INDEX UX_People_PersonID ON dbo.People (PersonID ASC)
INCLUDE (Name);

SQL was happily using this new index for the table joins, doing now an index seek instead of index scan, using the PersonID as key to output the Name column (included in the index).

Now... to the actual question, or questions:

Should I make this index UNIQUE as I did? (It kind of made sense to me at the time, one reason being the fact that the PersonID column would never have duplicate values anyway since it's the primary key)
Does it help SQL Server in any way by defining it UNIQUE or should it be a simple index?
Do I still need the clustered index to be the PersonID as well?
Is it a problem to have two indexes on the same column like this?
Since SQL benefits from having this non clustered index like I described for most of the case scenarios that I use, should I make the cluster index on some other column instead of PersonID?

Please let me know what your thoughts are... if I'm on the right path here or there are better approaches to solve this (duplication and uniqueness)...

David Browne - Microsoft · Accepted Answer · 2020-07-16 22:35:23Z

Should I make this index UNIQUE as I did?

Yes

Does it help SQL Server in any way by defining it UNIQUE or should it be a simple index?

No.

It's unique, so you should declare it as unique. It makes no difference to the actual implementation however. PersonID is the "row locator" for this table as it's the clustered index key, so it would be added to any non-unique non-clustered index as a key column. But since it's already there, physical storage doesn't change whether or not you declare the index as unique.

Do I still need the clustered index to be the PersonID as well?

You still need some clustered index, and PersonID is probably a good choice. Note that if you change the clustered index to something else, then you must make the index on PersonID unique.

Is it a problem to have two indexes on the same column like this?

It's not a problem. The clustered index has all the columns in it, so scanning it can be expensive. The seperate index on PersonID is useful for counting the rows, for paging, and (as here) for covering queries that need only the PersonID and Name.

Collectives™ on Stack Overflow

Non clustered index on the same column as the clustered primary key should be unique?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related