Compatible datatypes for join columns

The optimizer considers an index for joined columns only when the column types are the same or when the datatype of the join column precedes the other column’s datatype in the datatype hierarchy. This means that the optimizer considers using the index on only one of the join columns, limiting the choice of join orders.

For example, this query joins columns of decimal and int datatypes:

select * 
from t1, t2 
where t1.decimal_col = t2.int_col

decimal precedes int in the hierarchy, so the optimizer can consider an index on t1.decimal_col, but cannot use an index on t2.int_col. The result is likely to be a table scan of t2, followed by use of the index on t1.decimal_col.

Table 2-4 shows how the hierarchy affects index choice for some commonly problematic datatypes.

Table 2-4: Indexes considered for mismatched column datatypes

Join column types

Index considered on column of type

money and smallmoney

money

datetime and smalldatetime

datetime

int and smallint

int

int and tinyint

int

smallint and tinyint

smallint