Determining the datatype hierarchy

Each system datatype has a datatype hierarchy, which is stored in the systypes system table. User-defined datatypes inherit the hierarchy of the system datatype on which they are based.

The following query ranks the datatypes in a database by hierarchy. In addition to the information shown below, your query results will include information about any user-defined datatypes in the database:

select name,hierarchy
from systypes
order by hierarchy

name                           hierarchy 
------------------------------ --------- 
floatn                                 1 
float                                  2 
datetimn                               3 
datetime                               4 
real                                   5 
numericn                               6 
numeric                                7 
decimaln                               8 
decimal                                9 
moneyn                                10 
money                                 11 
smallmoney                            12 
smalldatetime                         13 
intn                                  14 
int                                   15 
smallint                              16 
tinyint                               17 
bit                                   18 
univarchar                            19 
unichar                               20 
sysname                               22 
varchar                               22 
nvarchar                              22 
char                                  23 
nchar                                 23 
timestamp                             24 
varbinary                             24 
binary                                25 
text                                  26 
image                                 27 
extended type                         99 
(31 rows affected)

The datatype hierarchy determines the results of computations using values of different datatypes. The result value is assigned the datatype that is closest to the top of the list.

In the following example, qty from the sales table is multiplied by royalty from the roysched table. qty is a smallint, which has a hierarchy of 16; royalty is an int, which has a hierarchy of 15. Therefore, the datatype of the result is an int:

smallint(qty) * int(royalty) = int