6

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.
jcolebrand
  • 6,354
  • 4
  • 42
  • 67
kacalapy
  • 2,052
  • 2
  • 27
  • 36

2 Answers2

10
  • 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

gbn
  • 69,809
  • 8
  • 163
  • 243
  • 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 09 '11 at 21:04
  • 4
    @kacalapy The fastest way on ten tables is to do it by hand – jcolebrand Mar 10 '11 at 03: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 06: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 06: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
  • @gbn and jcolebrand: I was just saying that in our work we have a high probability of having that issue in the future, with another db. And having this done in a dynamic script would have his time saved in the future. I was not saying for every db deploy, but just for his work time. Scripting something would save future work. – Marian Mar 11 '11 at 09:32
4

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:

http://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/

Thanks, Jeff

Jeff
  • 1,035
  • 7
  • 12