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 (
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