Compound Primary Key - Index both fields?

Question

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?

Answer 1

A query use the index in the column order (UserId first then RoleId). Without an index on RoleId, it will scan the clustered index. Unless there is a where clause with a userId, the engine does not know how to get inside the index whitout scanning all the userId.

Because roleId is a FK, best practice suggest to have an index on it. You definetly need it if you (often) query it.

Index on UserId alone is useless. The clustered index already does it since UserId is the entry point. Using it does not require an extra lookup to the PK in order to get data or associated roleId. Just clustered index seek.