If there is a datatype mismatch problem with a search argument on an indexed column, the query can use another index if there are other search arguments or it can perform a table scan. showplan output displays the access method and keys used for each table in a query.
You can use dbcc traceon(302) to determine whether an index is being considered. For example, using an integer variable as a search argument on int_col produces the following output:
Selecting best index for the SEARCH CLAUSE:
t1.int_col = unknown-value
SARG is a local variable or the result of a function or an expression, using the total density to estimate selectivity.
Estimated selectivity for int_col,
selectivity = 0.020000.
Using an incompatible datatype such as money for a variable used as a search argument on an integer column does not produce a “Selecting best index for the SEARCH CLAUSE” block in dbcc traceon(302) output, indicating that the index is not being considered, and cannot be used. If an index is not used as you expect in a query, looking for this costing section in dbcc traceon(302) output should be one of your first debugging steps.
The “unknown-value” and the fact that the total density is used to estimate the number of rows that match this search argument is due to the fact that the value of the variable was set in the batch; it is not a datatype mismatch problem.
See “SARGs using variables and parameters” for more information.
