One common problem when queries fail to use indexes as expected is datatype mismatches. Datatype mismatches occur:
With search clauses using variables or stored procedure parameters that have a different datatype than the column, for example:
where int_col = @money_parameter
In join queries when the columns being joined have different datatypes, for example:
where tableA.int_col = tableB.money_col
Datatype mismatches lead to optimization problems when they prevent the optimizer from considering an index. The most common problems arise from:
Comparisons between the integer types, int, smallint and tinyint
Comparisons between money and smallmoney
Comparisons between datetime and smalldatetime
Comparisons between numeric and decimal types of differing precision and scale
Comparisons between numeric or decimal types and integer or money columns
To avoid problems, use the same datatype (including the same precision and scale) for columns that are likely join candidates when you create tables. Use a matching datatype for any variables or stored procedure parameters used as search arguments. The following sections detail the rules and considerations applied when the same datatype is not used, and provide some troubleshooting tips.
