Datatypes of mixed-mode expressions  Determining precision and scale

Chapter 1: System and User-Defined Datatypes

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 
uintn                                 15
bigint                                16 
ubigint                               17
int                                   18
uint                                  19
smallint                              20
usmallint                             21
tinyint                               22 
bit                                   23 
univarchar                            24 
unichar                               25 
unitext                               26
sysname                               27 
varchar                               27 
nvarchar                              27 
longsysname                           27
char                                  28 
nchar                                 28 
timestamp                             29 
varbinary                             29 
binary                                30 
text                                  31 
image                                 32 
date                                  33 
time                                  34 
daten                                 35 
timen                                 36 
extended type                         99 

Noteu<int type> is an internal representation. The correct syntax for unsigned types is unsigned {int | integer | bigint | smallint }

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 or has the least hierarchical value.

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 20; royalty is an int, which has a hierarchy of 18. Therefore, the datatype of the result is an int:

smallint(qty) * int(royalty) = int




Copyright © 2005. Sybase Inc. All rights reserved. Determining precision and scale

View this book as PDF