The datatype hierarchy controls the use of indexes when search arguments or join columns have different datatypes. The following query prints the hierarchy values and datatype names:
select hierarchy, name from systypes order by 1
hierarchy name --------- ------------------------------ 1 floatn 2 float 3 datetimn 4 datetime 5 real 6 numericn 7 numeric 8 decimaln 9 decimal 10 moneyn 11 money 12 smallmoney 13 smalldatetime 14 intn 15 int 16 smallint 17 tinyint 18 bit 19 univarchar 20 unichar 21 reserved 22 varchar 22 sysname 22 nvarchar 23 char 23 nchar 24 varbinary 24 timestamp 25 binary 26 text 27 image
If you have created user-defined datatypes, they are also listed in the query output, with the corresponding hierarchy values.
The general rule is that when different datatypes are used, the systypes.hierarchy value determines whether an index can be used.
For search arguments, the index is considered when the column’s datatype is same as, or precedes, the hierarchy value of the parameter or variable.
For a join, the index is considered only on the column whose systypes.hierarchy value is the same as the other column’s, or precedes the other column’s in the hierarchy.
When char and unichar datatypes are used together, char is converted to unichar.
The exceptions are:
Comparisons between char and varchar, unichar and univarchar, or between binary and varbinary datatypes. For example, although their hierarchy values are 23 and 22 respectively, char and varchar columns are treated as the same datatype for index consideration purposes. The index is considered for both columns in this join:
where t1.char_column = t2.varchar_column
char columns that accept NULL values are stored as varchar, but indexes can still be used on both columns for joins.
The null type of the column has no effect, that is, although float and floatn have different hierarchy values, they are always treated as the same datatype.
Comparisons of decimal or numeric types also take precision and scale into account. This includes comparisons of numeric or decimal types to each other, and comparisons of numeric or decimal to other datatypes such as int or money.
See “Comparison of numeric and decimal datatypes” for more information.