Inner join using two fields: use one or two indexes?

Question

I have this escenario:

TableA: +2M rows.
TableB: +60K rows.

I need to INNER JOIN them by two fields ([Month] AS INT, [Year] AS INT).

I'm looking to improve the performance of that query; for that I could create an Index for [Month, Year] or I could create two sepparate indexes for [Month] and [Year]. What criteria should I consider to choose one of these two approaches?

Answer 1

Will you ever search for year without also searching for month? What about searching for month without ever searching for year?

If you will never search for one of these columns without also searching for the other, then create an index including both columns.

If you will only ever search for month, or month and year, then create an index for month, year, noting that month is the leading edge of the index.

If you will search for each column individually at times, create two separate indexes, one for each column... Sql Server can effectively use both these indexes if searching for both columns at the same time.