CODEWITHSUNDEEP
sql-servert-sqlprimary-keycovering-index
Mori
Mori

Reputation: 2574

The difference between A none clustered primary-key and a covering index in terms of performance

I have a table which contains a nonclustered 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.

Update:

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 NONE 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 uniqueidentifier.

What I am wondering is if that has any negative effects on anything else.

Execution Plan Before:

enter image description here

Execution Plan After:

enter image description here

Upvotes: 1

Views: 182

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

It's fundamentally unusual, but there could be some rare situation where it's fine.

You haven't told us anything about the reason for wanting to do this, so we can't really offer more than that.

EDIT:

According to this article by Kimberly Tripp, Foreign Keys can reference the columns of a UNIQUE INDEX that INCLUDE columns to cover queries.

To me, this means that it is probably fine to replace your Primary Key with a Unique Covering Index. The only negative implication of this I can think of is that any application or query that looks at the meta data of your table to find the Primary Key will not find one, and may not be smart enough to use the Alternate Key instead. For example, if you ever built a cube on this table, the Data Source View wizard might not automatically create the relationship.

Upvotes: 1

Related Questions