Are individual indexes necessary on columns that are included in a compound primary key?

Question

I have this M2M join table:

CREATE TABLE [dbo].[RecipientsDonors]
(
  [RecipientId] [int] NOT NULL,
  [DonorId] [int] NOT NULL,
  CONSTRAINT [PK_RecipientsDonors] PRIMARY KEY CLUSTERED 
  (
    [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?

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)
WITH (DROP_EXISTING = ON);