Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.
share|improve this question
up vote 9 down vote accepted
  • 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

share|improve this answer
whats the simplest/ fastest way to st these up? i have about 10 such tables i want of add the 2 index on each. – kacalapy Mar 9 '11 at 21:04
@kacalapy The fastest way on ten tables is to do it by hand – jcolebrand Mar 10 '11 at 3:46
@jcolebrand: the fastest way is to assume that he is going to need it in the future too ;). So create a dynamic script that will make these 2 indexes for all tables that follow this pattern. – Marian Mar 10 '11 at 6:30
@Marian: I'd have my CREATE INDEX statements with my CREATE TABLE which in turn have the PK defs contained therein. This should be a one off exercise for 10 indexes only (the reverse ones) as tables should already have a PK – gbn Mar 10 '11 at 6:33
@Marian for 10 tables it's a one-off. For something that has to be done again and again just add it to the regular scripts for table creations. If he needed to reapply it every single time, then I would suggest a sproc then. – jcolebrand Mar 10 '11 at 16:51

In SQL Server, non-clustered indexes include the clustering key, so I would offer the following alternative solution based on the previous solution offered:

  • clustered index (or clustered primary key) on userid, roleid
  • non-clustered index on roleid

This is assuming that userid would be the more likely search/scan candidate.

If a query is run with a filter condition based on userid, then the clustered index would meet the needs; if a query is run with a filter condition based on roleid, the non-clustered index would cover the query; if a query uses both roleid and userid to filter then the clustered index would cover the query.

Please reference the following article information on how the clustering key is included in non-clustered indexes:

Thanks, Jeff

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.