If you suspect that an index is not being considered on one side of a join due to datatype mismatches, use dbcc traceon(302). In the output, look for “Selecting best index for the JOIN CLAUSE”. If datatypes are compatible, you see two of these blocks for each join; for example:
Selecting best index for the JOIN CLAUSE: t1.int_col = t2.int_col
And later in the output for the other table in the join:
Selecting best index for the JOIN CLAUSE: t2.int_col = t1.int_col
For a query that compares incompatible datatypes, for example, comparing a decimal column to an int, column, there is only the single block:
Selecting best index for the JOIN CLAUSE: t1.decimal_col = t2.int_col
This means that the join costing for using an index with t2.int_col as the outer column is not performed.
