I have this M2M join table:

CREATE TABLE [dbo].[RecipientsDonors]
  [RecipientId] [int] NOT NULL,
  [DonorId] [int] NOT NULL,
    [RecipientId] ASC,
    [DonorId] ASC

I also have these two indexes:

CREATE NONCLUSTERED INDEX [IX_RecipientsDonors_RecipientId] ON [dbo].[RecipientsDonors]
  [RecipientId] ASC

CREATE NONCLUSTERED INDEX [IX_RecipientsDonors_DonorId] ON [dbo].[RecipientsDonors]
  [DonorId] ASC

My intent with the two indexes is to speed single-column lookups.

Given the existence of the primary key, are the indexes redundant? Or instead are they necessary because the primary key includes both columns?

1 Answer 1


An index can always be used by just a leading key. So the IX_RecipientsDonors_RecipientId index is not really necessary, as the primary key already covers the same columns.

While it will slightly increase performance of single column scans (due to a slightly narrower index), the likelihood of such a scan is low (why would you scan if you can seek?), and the likelihood of querying only a single column is low anyway. Furthermore, there is also the cost of the index in keeping it up-to-date with the table on every insert, update and delete, as well as the storage cost of storing and backing it up.

DROP INDEX [IX_RecipientsDonors_RecipientId] ON dbo.RecipientsDonors;

The other index IX_RecipientsDonors_DonorId however, should remain. It is necessary both for regular seeks on that column, and because it will be heavily used in foreign key lookups whenever the parent table row is deleted.

But it would be wise to add the other column to it, so that you can fully cover queries which seek to DonorId but want RecipientId results. It should also be unique so that the compiler can infer optimizations.

This should be the normal setup for every two-column many-many join table you create: a primary key on both columns, and a secondary unique index with the key columns in the opposite order.

CREATE UNIQUE NONCLUSTERED INDEX [IX_RecipientsDonors_DonorId] ON dbo.RecipientsDonors
  (DonorId, RecipientId)
  • That's very interesting, thank you! Does it make a difference that I'll never be doing any deletes? Does the same rule still apply? I believe it still does, but I just want to be sure.
    – InteXX
    Jun 22 at 1:28
  • @InteXX If you ever need to query with a predicate (filter from a WHERE or JOIN clause) on the second column, then without an index that leads with that column, the entire table needs to be scanned. So yes, it's helpful to have a second index that leads with the second column of the first index, and probably generally helpful to include the first column of the first index in the second index too.
    – J.D.
    Jun 22 at 1:29
  • @InteXX No I don't think so. Foreign key child dependencies are referenced only by a DELETE on the parent table (or an UPDATE which changes the PK which you should never do). As mentioned, there are often other benefits though. To make it clear, your primary key is already a clustered index over both columns. Jun 22 at 1:31
  • @Charlieface Perfect. That's what I'll be doing then. And thanks for that extra clarification.
    – InteXX
    Jun 22 at 1:40
  • @J.D. Very good, thank you. That's helpful.
    – InteXX
    Jun 22 at 1:40

Your Answer

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

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