Sql-server – Implications of using a Unique NonClustered Index with Covering Columns instead of a Primary Key

sql serversql-server-2008

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.

Best Answer

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.

Related Question