Overview of the datatype hierarchy and index issues

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.