Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 6 Using and Creating Datatypes

Mixed-mode arithmetic and datatype hierarchy

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

Working with money datatypes

If you are combining money and literals or variables, and you need results of money type, use money literals or variables:

create table mytable
(moneycol money,)

insert into mytable values ($10.00)

select moneycol * $2.5 from mytable

If you are combining money with a float or numeric datatype from column values, use the convert function:

select convert (money, moneycol * percentcol)
    from debits, interest

drop table mytable

Determining precision and scale

For the numeric and decimal types, 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 s2, Adaptive Server determines the precision and scale of the results as shown in Table 6-4:

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)


	Expand Search