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
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.
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!
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???
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.
6 People are following this question.
