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