How to index a many to many table most effectively

Should a many to many table be indexed? What kind of index would be best?

Here's an example table:

CREATE TABLE user_role (
  userId INT,
  roleId INT
)

--edit: drachenstern - I added the table def based on the original comments, and assumed ints.

• a clustered index on (userid, roleid)

• another index on (roleid, userid)

You don't need a surrogate key (unless you use a braindead ORM) and you almost always need the 2nd index

StackExchange Database Administrators Q#1654, answer score: 10