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.

The exceptions are: