I have a many-to-many join table between users and roles.
CREATE TABLE AppUserRole
(
UserId INT NOT NULL,
RoleId INT NOT NULL,
CONSTRAINT PK_AppUserRole PRIMARY KEY (UserId, RoleId),
CONSTRAINT FK_AppUserRole_User FOREIGN KEY (UserId)
REFERENCES AppUser(UserId),
CONSTRAINT FK_AppUserRole_Role FOREIGN KEY (RoleId)
REFERENCES AppRole(RoleId)
);
CREATE INDEX IX_AppUserRole_RoleId ON AppUserRole(RoleId)
CREATE INDEX IX_AppUserRole_UserId ON User(UserId)
Since the compound key is clustered in the order of (UserId
, RoleId
), is the second index on UserId
really necessary?
When you query for all rows for a specific UserId
it's going to use the clustered index. Only when you query for all users in a specific RoleId
will the index IX_AppUserRole_RoleId
be used.
Is the index IX_AppUserRole_UserId
necessary?