If a join between different datatypes is unavoidable, and it impacts performance, you can, for some queries, force the conversion to the other side of the join. In the following query, an index on smallmoney_col cannot be used, so the query performs a table scan on huge_table:
select *
from tiny_table, huge_table
where tiny_table.money_col =
huge_table.smallmoney_col
Performance improves if the index on huge_table.smallmoney_col can be used. Using the convert function on the money column of the small table allows the index on the large table to be used, and a table scan is performed on the small table:
select *
from tiny_table, huge_table
where convert(smallmoney,tiny_table.money_col) =
huge_table.smallmoney_col
This workaround assumes that there are no values in tinytable.money_col that are large enough to cause datatype conversion errors during the conversion to smallmoney. If there are values larger than the maximum value for smallmoney, you can salvage this solution by adding a search argument specifying the maximum values for a smallmoney column:
select smallmoney_col, money_col
from tiny_table , huge_table
where convert(smallmoney,tiny_table.money_col) =
huge_table.smallmoney_col
and tiny_table.money_col <= 214748.3647
Converting floating-point and numeric data can change the meaning of some queries. This query compares integers and floating-point numbers:
select *
from tab1, tab2
where tab1.int_column = tab2.float_column
In the query above,you cannot use an index on int_column. This conversion forces the index access to tab1, but also returns different results than the query that does not use convert:
select * from tab1, tab2 where tab1.int_col = convert(int, tab2.float_col)
For example, if int_column is 4, and float_column is 4.2, the modified query implicitly converts to a 4, and returns a row not returned by the original query. The workaround can be salvaged by adding this self-join:
and tab2.float_col = convert(int, tab2.float_col)
This workaround assumes that all values in tab2.float_col can be converted to int without conversion errors.
