Home

	Expand Search

Reference Manual

Chapter 1: System and User-Defined Datatypes

Datatype of Mixed-Mode Expressions

Datatype of Mixed-Mode Expressions

When you perform concatenation or mixed-mode arithmetic on values with different datatypes, Adaptive Server must determine the datatype, length, and precision of the result.

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
varchar                               19
sysname                               19
nvarchar                              19
char                                  20
nchar                                 20
varbinary                             21
timestamp                             21
binary                                22
text                                  23
image                                 24
(28 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

Determining Precision and Scale

For numeric and decimal datatypes, each combination of precision and scale is a distinct Adaptive Server datatype. If you perform arithmetic on two numeric or decimal values:

• n1 with precision p1 and scale s1, and
• n2 with precision p2 and scale n2

Adaptive Server determines the precision and scale of the results as shown in Table 1-3:

Table 1-3: Precision and scale after arithmetic operations

Operation	Precision	Scale
n1 + n2	max(s1, s2) + max(p1 -s1, p2 - s2) + 1	max(s1, s2)
n1 - n2	max(s1, s2) + max(p1 -s1, p2 - s2) + 1	max(s1, s2)
n1 * n2	s1 + s2 + (p1 - s1) + (p2 - s2) + 1	s1 + s2
n1 / n2	max(s1 + p2 + 1, 6) + p1 - s1 + p2	max(s1 + p2 -s2 + 1, 6)