Implications of using a Unique NonClustered Index with Covering Columns instead of a Primary Key

Question

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:

ALTER TABLE [MyTable]
    ADD CONSTRAINT [PK_MyTable] 
    PRIMARY KEY NONCLUSTERED ([KeyColumn])
GO

AND

CREATE UNIQUE NONCLUSTERED INDEX [IX_MyTable_SomeIndex] 
    ON [MyTable] ([KeyColumn]) 
    INCLUDE ([Column1], [Column2])
GO

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.