• Effectively, the use of an index in the ON clause is the same as the use of an index in the WHERE clause. The criteria can act as a filter and can take advantage of the statistics and the index storage as a means of accelerating access. In addition, having the index store the data in the same order that is needed by the query can help speed up joins in some cases. Also, for joins, look to making sure you have enforced (WITH CHECK) foreign keys in place. This is also taken into account by the optimizer.

    If you want lots of details on these discussions, please see my book.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning