SQL Server suggestion to create nonclustered index - on 2 columns, reversed

I have a query, where SQL Server 2012 suggested a create a nonclustered index as follows:

CREATE NONCLUSTERED INDEX []
ON [dbo].[tblUserCourses] ([courseID])
INCLUDE ([userID])

I created the index with this code.

Running the query, it now is suggesting a similar index, but with the INCLUDE reversed:

CREATE NONCLUSTERED INDEX []
ON [dbo].[tblUserCourses] ([userID])
INCLUDE ([courseID])

Excuse my ignorance... is there a (significant) difference between these 2, or is this one of the cases I am reading, where the SQL Server index suggestions may be off?

Many thanks

UPDATE:

Here is the query - its a pretty basic query, getting a list of courses, and the number of people (men/women/paid/not paid) for each course. Would be happy to hear if there is a better way...

Interestingly, adding in the eventID = 2 at the very end, made the query run in 2 mins+ rather than 4 seconds without that filter!

```
select C.*
,(
select COUNT(*)
from tblUserCourses UC
inner join tblUserEvents on UC.UserID = tblUserEvents.UserId
inner join tblCourses on tblCourses.courseID = UC.courseID
where UC.courseID = C.CourseID
and tblUserEvents.StatusID = 50
and tblUserEvents.EventID = C.eventID
) as PaidParticipants
,(
select COUNT(*)
from tblUsers U
inner join tblUserEvents on U.UserID = tblUserEvents.UserId
inner join tblUserCourses UC on U.UserID = UC.UserId
inner join tblCourses on tblCourses.courseID = UC.courseID
where UC.courseID = C.CourseID
and tblUserEvents.StatusID = 50
and tblUserEvents.EventID = C.eventID
and UPPER(U.gender) = 'M'
) as Males
,(
select COUNT(*)
from tblUsers U
inner join tblUserEvents on U.UserID = tblUserEvents.UserId
inner join tblUserCourses UC on U.UserID = UC.UserId
inner join tblCourses on tblCourses.courseID = UC.courseID

Yes there is a big difference between both indices.

The following index will use the courseID as a seek predicate. This will help SQL Server to find all pages with a defined courseID. In addition, the column userID is included in the pages of the index. This will allow SQL Server to serve those values directly from the index.

CREATE NONCLUSTERED INDEX []
ON [dbo].[tblUserCourses] ([courseID])
INCLUDE ([userID])

The creation of this index will help SQL Server to perform quick responses for Queries like this:

SELECT userID FROM yourTable WHERE courseID = 1

The courseID can be used for the search in the index. Where the userID is served from the leaf level pages from the index.

Where as the following index works exactly the same but the other way round. It will use the userID for the seek and can provide the courseID directly from the leaf level pages from the index.

CREATE NONCLUSTERED INDEX []
ON [dbo].[tblUserCourses] ([userID])
INCLUDE ([courseID])

The index will improve the performance of this query:

SELECT courseID FROM yourTable WHERE userID = 1

SQL Server may also use indices and join the results of both indices.
In example, you can serve this query totally from the indices:

SELECT userID, courseID FROM yourTable

This will be served by a hash match using the both indices.

Another one which could benefit from both together may be this one:

SELECT COUNT(*) FROM yourTable WHERE userID=2 and courseID=10

It might happen, that your SQL Server provides an index hint and sees that he could perform better by using a special index. After you created those index, he recognizes (during the new index) that another one would be even better. This isn't uncommon.

This may also happen if your query optimizer runs into a timeout. This can happen very fast. In the case your query optimizer times out, SQL Server will use the best plan he found until the timeout. This can vary and this may have some bad effects. Using the indices, you help your query optimizer and he will use it's time more efficient. To provide good plans.

To really see if your indices are a good choice, you need to show a bit more of your code. Additionally you can try to evaluate your index usage. There is a good DMV for this called dm_db_index_usage_stats. It will provide you how many reads, seeks, updates, etc. happend on an index. This will help you to get your own decisions if it's good or bad. An index is mostly on then useful if you read it more than you write it.

SELECT * from sys.dm_db_index_usage_stats

Hopefully this will help to clear things up for you.

Edit
Due to the lastly provided information, I would suggest to try an index based on EventID, CourseID (same order!).
On your other table maybe an index over EventID, StatusID too.

StackExchange Database Administrators Q#106113, answer score: 2