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