Are individual indexes necessary on columns that are included in a compound primary key?
3
votes
1
answer
450
views
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?
Asked by InteXX
(559 rep)
Jun 21, 2023, 11:08 PM
Last activity: Jun 22, 2023, 01:50 AM
Last activity: Jun 22, 2023, 01:50 AM