User's Guide

Part VI. SQL Anywhere Reference

Chapter 41. SQL Anywhere Data Types

Purpose

For storing numerical data.

Syntax

     Numeric data types
          DECIMAL [ ( precision [ , scale ] ) ]
          | DOUBLE
          | FLOAT [ ( precision ) ]
          | INT
          | INTEGER
          | NUMERIC [ ( precision [ , scale] ) ]
          | REAL
          | SMALLINT
          | TINYINT

Description

DECIMAL [(precision[,scale])] A decimal number with precision total digits and with scale of the digits after the decimal point. The defaults are scale = 6 and precision = 30.

These defaults can be changed with the SET statement (see "SET OPTION statement").

The storage required for a decimal number can be computed as:

     2 + int( (before+1) / 2 ) + int( (after+1)/2 )

where int takes the integer portion of its argument, and before and after are the number of significant digits before and after the decimal point. Note that the storage is based on the value being stored, not on the maximum precision and scale allowed in the column.

DOUBLE A double precision floating-point number stored in 8 bytes. The range of values is 2.22507385850720160e-308 to 1.79769313486231560e+308. Values held as DOUBLE are accurate to 15 significant digits, but may be subject to round-off error beyond the fifteenth digit.

FLOAT [ ( precision ) ] If precision is not supplied, the FLOAT data type is the same as the REAL data type. If precision supplied, then the FLOAT data type is the same as the REAL or DOUBLE data type, depending on the value of the precision. The cutoff between REAL and DOUBLE is platform dependent.

When a column is created using the FLOAT(precision) data type, columns on all platforms are guaranteed to hold the values to at least the specified minimum precision. In contrast, REAL and DOUBLE do not guarantee a platform-independent minimum precision.

INT Signed integer of maximum value 2,147,483,647 requiring 4 bytes of storage.

INTEGER Same as INT.

NUMERIC [(precision[,scale])] Same as DECIMAL.

REAL A single precision floating-point number stored in 4 bytes. The range of values is 1.175494351e-38 to 3.402823466e+38. Values held as REAL are accurate to 6 significant digits, but may be subject to round-off error beyond the sixth digit.

SMALLINT Signed integer of maximum value 32,767 requiring 2 bytes of storage.

TINYINT Signed integer of maximum value 255 requiring 2 bytes or 4 bytes of storage.

Notes

• The INTEGER, NUMERIC and DECIMAL data types are sometimes called exact numeric data types, in contrast to the approximate numeric data types FLOAT, DOUBLE, and REAL. The exact numeric data types are those for which precision and scale values can be specified, while approximate numeric data types are stored in a predefined manner. Columns holding exact numeric data are accurate after arithmetic operations to the least significant digit specified.
• In SQL Anywhere TINYINT columns should not be fetched into Embedded SQL variables defined as char or unsigned char, since the result is an attempt to convert the value of the column to a string and then assign the first byte to the variable in the program.
• Before release 5.5, hexadecimal constants greater than four bytes were treated as string constants, and others were treated as integers. The new default behavior is to treat them as binary type constants. To use the historical behavior, set the TSQL_HEX_CONSTANTS database option to OFF.