Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a large table [MyTable] which currently has both a PK, and a UNIQUE Non Clustered Index on [KeyColumn] (which has additional covering columns), which seems redundant, and I think I can delete the Primary Key and instead use 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 MyTable.KeyColumn

The only implication I can think of doing this 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.

share|improve this question

migrated from stackoverflow.com Jul 26 '12 at 13:45

This question came from our site for professional and enthusiast programmers.

    
Could you comment on what you're trying to accomplish? You should also make sure you end up with a clustered index in the end. –  jn29098 Jul 26 '12 at 11:36
    
@jn29098 - Updated. Confirmed that we do have a clustered index, on columns which are neither the PK nor the covering columns, so this doesn't seem relevant to this decision. –  StuartLC Jul 26 '12 at 11:43
1  
You sure you don't have any tool/ORM/data generation modeling tool that will barf when it sees the PK gone? –  Remus Rusanu Jul 26 '12 at 11:58
    
Only disadvantage i see is that once PK is gone..then you have just one index on keycolumn and the queries which is using PK index say for index scan on keycolumn or order by keycolumns and are not covered by Uniuqe index might clock somewhat extra IO's as unique index leaf pages are much more than that of the PK.Otherwise it looks fine.But some purist will say you should have PK :) –  Gulli Meel Jul 26 '12 at 12:19
1  
I would suggest that you check the usefulness of that index using the index usage stats DMV and see whether it is being used by some of your queries.Check it against the unique key index as well and then deicde what will happen to the query using that index.. –  Gulli Meel Jul 26 '12 at 12:22

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
    
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 Jul 30 '12 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.

share|improve this answer
    
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 Jul 26 '12 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. –  Matt Whitfield Jul 26 '12 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 coloumns 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.

share|improve this answer
2  
From the first paragraph: The table is clustered by another column entirely. –  JNK Jul 26 '12 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. –  Matt Whitfield Jul 26 '12 at 15:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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