SQL Index on two columns

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?

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

• SQL: Do you need an auto-incremental primary key for Many-Many tables?
• SQL - many-to-many table primary key
• Difference between 2 indexes with columns defined in reverse order

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

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

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.

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.