We have a large table
[MyTable] which currently has both a PK, and a UNIQUE Index on
[KeyColumn] (which has additional covering columns), which seems redundant. The table is clustered by another column entirely.
So we have:
ALTER TABLE [MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED ([KeyColumn]) GO
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:
- Drop the Foreign Key Constraints reliant on the MyTable
- Drop the Primary Key on MyTable entirely
- Re-Add the foreign keys reliant on MyTable
The only implication I can think of is that we won’t get a key symbol on our ERD diagrams, and that the index density will be less because of the included columns.
I've read http://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.