Datatype mismatches and query optimization

One common problem when queries fail to use indexes as expected is datatype mismatches. Datatype mismatches occur:

Datatype mismatches lead to optimization problems when they prevent the optimizer from considering an index. The most common problems arise from:

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.