Sql-server – How to index a many to many table most effectively

sql-server-2005

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.

Best Answer

  • 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

Related Question