I have a table which contains a non-clustered primary key. I intend to remove this primary key and create a unique Covering Index on the same column instead.
Then The table won't have a primary key, but a unique covering index.
I searched google but could not find a relevant topic. Is that fundamentally wrong? or it's ok to be done.
Why do I need to convert this index to a covering index?
Because this is the primary key and I have many select queries based on this key. At the moment for each select, there will be a need for a RID Lookup because this is a non-clustered key. If I convert it to a covering index, this RID Lookup will be gone for those select queries and hopefully there will be better performance (at least the excecution plan says that).
Of course integrity is also intended and that's why I will make it a unique covering index. The primary key is a
uniqueidentifier, generated by the application (which explains why we don't make it clustered)..
What I am wondering is if that has any negative effects on anything else.
Execution Plan Before:
Execution Plan After:
FYI, the primary-key is a
uniquidentifier which is generated in the code (application). In load tests we came up with the conclusion of making it non-clustered.