When you perform arithmetic on values with different datatypes, Adaptive Server must determine the datatype and, in some cases, the length and precision, of the result.
Each system datatype has a datatype hierarchy, which is stored in the systypes system table. User-defined datatypes inherit the hierarchy of the system type 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 reserved 21 varchar 22 sysname 22 nvarchar 22 char 23 nchar 23 varbinary 24 timestamp 24 binary 25 text 26 image 27 (30 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
