SQL Index on two columns

Question

Here's my simple scenario:

I've a Users table and a Locations table. ONE User can be related to MANY Locations so I've a UserLocation table which is as follows:

ID (int-Auto Increment) PK
UserID (Int FK to the Users table)
LocID (Int FK to the Locations table)

Now, as ID is the PK it is Indexed by default in SQL-Server. I was a bit confused about the other two columns:

OPT 1: Shud I define an Index on both the columns like: IX_UserLocation_UserID_LocID

OR

OPT 2: Shud I define two separate Indexes like : IX_UserLocation_UserID & IX_UserLocation_LocID

Pardon me if both do the same - in that case pls explain. If not - which one is better and why?

Do you really need the auto incrementing surrogate `id` field in the junction table? What is it there for? I'd make a composite Primary key on the 2 FK columns. What queries will be running against that table? Will they be looking up by `user`, `location` or both? — Martin Smith, May 24 '11 at 13:26
Thank you guys. Just FYI - the ID column is our tradition we keep it in all the tables, at most places it serves the purpose of unique record identifier. I know here UserID + LocID can be a composite PK but to simplify & standardize we keep the ID field. IF this is not convincing - forget the ID key for now! — Hemant Tank, May 31 '11 at 14:44

score 3 · Answer 1 · edited May 23 '17 at 10:34

You need

2 columns
- UserID (Int FK to the Users table)
- LocID (Int FK to the Locations table)
One PK on both (UserID, LocID)
Another index on the reverse (LocID, UserID)

You may not need both indexes but it's rare

Edit, some links to other SO answers

score 2 · Answer 2 · answered May 24 '11 at 13:32

There are several things we hire the database for. One is fast information retrieval and another is declarative referential integrity (DRI).

If you requirement is that a user may be related to a given location only once then you want a unique index on UserID & LocatonID.

If your question is how to retrieve the data fast the answer is -- it depends. How are you accessing the data? If you always get the entire set of locations for a user then I would probably use a clustered non-unique index on UserID. If your access is "who is in locatin x?" then you probably want a clustered non-unique index on LocationID.

If you ask both questions you'll probably want both indexes (although you only get 1 clustered, so the 2nd index may want to use an INCLUDE to grab the other column).

Either way, you probalby don't want ID as your clustered index (the default when marking a column as PK in SSMS table designer).

HTH, -eric

score 1 · Answer 3 · answered May 24 '11 at 13:36

1

In addition to the "gbn" answer. It will depend on the Where clause. Whether you are using user or location or both

answered May 24 '11 at 13:36

Pankaj

9,749
32
139
283

score 0 · Answer 4 · answered May 24 '11 at 13:31

0

The best way to setup all the indexed you think you need on dev and check look at the query plans of the queries your app runs and see what indexes get read.

answered May 24 '11 at 13:31

Justin Dearing

14,270
22
88
161

score 0 · Accepted Answer · answered May 24 '11 at 13:32

0

You should probably create two separate indexes. One thing that is often forgotten with foreign keys is the fact that deleting a user might cascade-delete the user-location relation in your table. If there is no index on userID, this might lead to a table-lock of your user-location relation. The same applies to deleting a location.

answered May 24 '11 at 13:32

Lukas Eder

211,314
129
689
1,509

Does having 2 separate indexes cover any where clause combination of UserID and LocID. Is there any difference in performance? – Renae Dec 06 '13 at 04:50

SQL Index on two columns

5 Answers5