question

satya avatar image
satya asked

Why Primary Key should not be placed in Non Clustered index other than PK index ?

Hi Everyone, In most of the Non clustered indexes, we placed primary key column as one of the columns in the index columns. Does it effect the Performance ? Please help me out. Regards, Satya
indexes
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It affects performance because the non-clustered index already has a pointer back to the clustered index, you are just adding more data for the seek/scan to traverse..the bigger issue is that it will affect ordering and (possibly) render the index useless.
0 Likes 0 ·
JohnM avatar image
JohnM answered
I would say, It Depends! Are your primary keys usually your clustering key as well? If so, then yes it could affect performance as the non-clustered indexes already have the clustering key contained within itself, just as Scot mentions. If your primary keys are NOT your clustering key, then I think you would have to evaluate the query plans to see what path the optimizer picked for the query to see if it makes sense to add the primary key columns. If the queries don't need the primary key for the results, then don't add it. Hope this helps!
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

good points
0 Likes 0 ·

Hi @JohnM I know this question is answered, but I didn't want to start a new thread, because this one, still, is exactly what I wanted to ask...

I'm looking at the missing indexes stat, and see that several suggested indexes have, in the list of included columns, the column (szTransactionId) which is a primary key for the table and a clustered index.

So... is there still a performance gain coming from such index???

0 Likes 0 ·
included.jpg (21.3 KiB)
Jeff Moden avatar image
Jeff Moden answered

The missing index suggestions are just that way. They must have been designed after what I do because there's no guarantee that someone isn't going to move, add to, or even delete the clustered index. It also means that you don't have to lookup the columns in the clustered index when you're troubleshooting, etc.

For those that think manually adding the names of the CI columns are adding additional copies of the columns, you're incorrect.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.