We have a large table [MyTable] which currently has both a Primary Key, and a Unique Non Clustered Index on the same column ([KeyColumn]). The U NC index also has additional covering columns.

Having both PK and Unique NC Index on the same columns seems redundant, so I was considering deleting the Primary Key and instead using the Unique Non Clustered index for Referential Integrity purposes.

Note that the table is clustered by another column entirely.

i.e. So we have:



    ON [MyTable] ([KeyColumn]) 
    INCLUDE ([Column1], [Column2])

As far as I know, it is not possible to add covering columns to a Primary Key, so I intend to do:

  • Drop the Foreign Key Constraints reliant on MyTable.KeyColumn
  • Drop the Primary Key on MyTable.KeyColumn entirely
  • Re-Add the foreign keys to the table (i.e. RI will be enforced via MyTable.KeyColumn)

The only implication I can think of doing this is that we won’t get the visual key symbol on our ERD diagrams, and that the (leaf) index density will be less because of the included columns.

I've read https://stackoverflow.com/questions/487314/primary-key-or-unique-index and am happy with the integrity and performance aspects of doing this.

My Question is : Is this approach flawed?

Edit What I'm trying to accomplish : Performance Optimisation and Spring Cleaning. By removing either the PK or an Index, there will be less pages needed for my indexes = faster writes, plus also maintenance/operational benefits, i.e. one less index to keep defragged etc.

To put some background to this, I've never before had a table which is being referenced, without a PK. However, the fact that a NC Index with covering columns was added to the table means that I need to adapt my thinking.

  • 1
    You sure you don't have any tool/ORM/data generation modeling tool that will barf when it sees the PK gone? Commented Jul 26, 2012 at 11:58

3 Answers 3


Performance Optimisation. By removing either the PK or an Index, there will be less pages needed for my indexes = faster writes, plus also maintenance/operational benefits, i.e. one less index to keep defragged etc.

You need to be able to prove that what's proposed will actually help (cost vs. benefit). For what reason is this change being considered? Are there actually performance issues with this table, or did it just "look wrong"?

Here are some other questions that will help you come to the best decision for your environment:

  • How much time would be saved in the maintenance window? In the backup window?

  • How much storage space would this save (data files, log files, backups, etc.)?

  • Is INSERT performance on this table really a bottleneck right now? How much would it improve? Is removing an index the best strategy to fix that problem?

  • Will this cause problems with database tools and frameworks (ORMs particularly) that expect each table to have a primary key and not just a unique index? Transactional Replication requires a primary key on published tables.

  • Is a self-documenting database schema important?

  • Despite its limited use, is the narrowness of the primary key index still allowing the optimizer to produce more efficient plans for certain queries? (Use sys.dm_db_index_usage_stats to find out.)

Personally speaking, from what you've told us, I would leave it alone until it can be proven that both (a) the extra index is a problem, and (b) removing it is the solution.

  • 1
    Thanks - it turns out that the index stats showed that the PK was still heavily used for scans. It seems I was being overzealous - the readability / convention benefits of retaining the PK should outweigh any storage implications in the long run. The point about transactional replication clinches it though - we WILL need to replicate this database shortly.
    – StuartLC
    Commented Jul 30, 2012 at 5:01

The only type of query on that table that will perform worse (and only slightly worse) will be those that request a range of KeyColumn values - as those queries at the moment would be best served by an index seek across your PK.

It would be worth bearing in mind that the cost of referential integrity checking for tables which reference this table will be higher (again only slightly higher).

As long as you take care of the nullable aspect, then you should be just fine with the one index.

If it was my DB, I'd probably go for the single unique index, all other things being equal.

  • Thanks - do you have any reference links? w.r.t. slightly worse, are you referring solely to the lower index density in the covering index, or is there something else which would cause this?
    – StuartLC
    Commented Jul 26, 2012 at 15:29
  • It's just from a lot of experience unfortunately, and chatting with people much smarter than me over the years! And yes, it is precisely that - there will be less rows per page, so more I/O. However - it's worth noting that INCLUDE columns are only present in the leaf level. So it's not all bad. I'm sure you know that based on the detail in your question, but I am guessing other readers may not. Commented Jul 26, 2012 at 15:52

As far as I know, it is not possible to add covering columns to a Primary Key

If you have a clustered index on KeyColumn, then since it's a clustered index, all the other columns are implicitly 'covered'. So you do not gain anything by adding another index on KeyColumn. In fact you will slow down your inserts and used more space.

This is because a clustered index is not an index as such, but just an instruction to store the rows of the table in the order of the index. And once you've foudn a row by primary key, then you have all other other columns right there.

  • 2
    From the first paragraph: The table is clustered by another column entirely.
    – JNK
    Commented Jul 26, 2012 at 14:31
  • 5
    I think there might be some primary key / clustered index confusion going on here. Despite management studio's best efforts to persuade you so, they are not the same thing. Commented Jul 26, 2012 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.