When Your Primary Key is Not the Clustered Index

Question

When Your Primary Key is Not the Clustered Index

m.sams

SSC Veteran

Points: 221
More actions
December 16, 2016 at 3:39 pm

#319761

Hi, We have tables that have primary keys that are not the clustered index. The primary key still gets used in some joins, but there is always a key lookup because you can't include columns on a primary key. It seems to me that one could remove the primary key index and create a new unique non-clustered index and then add the necessary include columns and have a more useful index. It is still an auto incrementing identity. Is there any down side to doing this?
Thanks!

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 1

m.sams (12/16/2016)
Hi, We have tables that have primary keys that are not the clustered index. The primary key still gets used in some joins, but there is always a key lookup because you can't include columns on a primary key. It seems to me that one could remove the primary key index and create a new unique non-clustered index and then add the necessary include columns and have a more useful index. It is still an auto incrementing identity. Is there any down side to doing this?
Thanks!

Sorry to play the old saw but "It Depends". Have a look at the index usage in sys.dm_db_index_usage_stats for the database(s), table(s), and index(es) involved.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

In addition to what Jeff said, you REALLY need to know your application's data and data access patterns to determine the optimal indexing strategy. There are certainly valid scenarios for non-clustered PK and some other clustered index. The most obvious one is sorts/group bys/range filtering is often done on the clustered index column(s) (in left-right order). Another is to facilitate a very common merge join on that table.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Ken McKelvey SSCoach Points: 18939 More actions · Answer 3

Another thing to add to mix is in high transaction environments where foreign keys look up primary keys on another table.

If the primary key on the other table is clustered, then one quite often has to add a non-clustered index on the same columns in order to avoid deadlocks.

If the primary key is non-clustered then this is not such a problem.

I would do a lot of testing before changing clustered keys on a production environment.

m.sams SSC Veteran Points: 221 More actions · Answer 4

Thanks for the replies. Good information!

I guess what I am asking, more specifically, is there anything special about the "Primary Key" index? By virtue of the column being the primary key, I cannot include columns. If I create an index that doesn't specify that the column is a primary key, but it is still the identity am I going to lose something that I am unaware of?

Example: I have a table with 100,000 rows. The ProductID column is the primary key, but not the clustered index. When the table is joined by the ProductID there is always a key lookup because there are no included columns. I want to drop the primary key index, add a new unique, non-clustered index on that column with include columns that prevent a key lookup. What do I lose by exchanging the Primary Key index with another non-clustered index?

Thanks again!

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 5

Well keep in mind a primary key is a constraint not an index, it can be misleading that SQL Server will create a clustered index on the primary key if you don't specify an explicit clustered index.

It sounds like what you're trying to do is create your primary key but not have your queries do lookups, to do that with an index what you could do is create the non clustered index and set all the columns to NOT NULL, then you would be able to include other columns in the index. You would have to look at your system and determine whether the key lookups are worse for performance than maintaining the included columns on an index.

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 6

m.sams (12/19/2016)
What do I lose by exchanging the Primary Key index with another non-clustered index?

Don't think of the non-clustered PK as an index. It is just a constraint which uses an index to validate uniqueness. Its purpose is to ensure data integrity, not performance.

Why can't you leave the PK as is and create an additional non-clustered index with the included columns?

If that doesn't solve your problem, you can delete the NCI to be back at your starting point.

Simple to implement, simple to rollback, minimal risk. Win-Win-Win.

Wes
(A solid design is always preferable to a creative workaround)

m.sams SSC Veteran Points: 221 More actions · Answer 7

There you have it. All the tools that I use to analyze index performance shows the constraint as an index. Thanks for the info!

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

m.sams (12/16/2016)
The primary key still gets used in some joins, but there is always a key lookup because you can't include columns on a primary key.

Is the key lookup a problem?

Yes, it's a slow operation, but if you're looking up a very small number of rows, it should be fine.

All the tools that I use to analyze index performance shows the constraint as an index.

It is an index. Indexes are used to enforce unique and primary key constraints. It's just that the primary purpose is the constraint and the secondary is using the index as an index.

Creating a second index is viable, but you need to test carefully and decide if it's really worth while, I seldom see cases where it is.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ScottPletcher SSC Guru Points: 101200 More actions · Answer 9

m.sams (12/16/2016)It seems to me that one could remove the primary key index and create a new unique non-clustered index and then add the necessary include columns and have a more useful index. It is still an auto incrementing identity. Is there any down side to doing this?

Only if something in SQL Server requires that it be an explicit PK (sadly SQL Server requires an explicit PK for certain things). Otherwise just a regular non-clus index because, as you've noted, it's much more flexible.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".