A numeric <data type> is defined by a descriptor that contains four pieces of information:
1. The <data type>'s name; either INTEGER, SMALLINT, NUMERIC, DECIMAL, FLOAT, REAL, or DOUBLE PRECISION.
2. The <data type>'s precision.
3. The <data type>'s scale (for exact numeric types).
4. Whether the <data type>'s precision and scale are expressed in decimal or binary terms.break
Page 51
INTEGER
The required syntax for an INTEGER <data type> specification is as follows.
INTEGER <data type> ::=
INTEGER
INTEGER may be abbreviated as INT; it defines a set of possibly signed whole numbers that
have a scale of zero.
[NON-PORTABLE] INT's precision must be greater than or equal to the precision of SMALLINT but is non-standard because the SQL Standard requires implementors to define INT's precision. FIPS says that INT should have a precision of at least 9 decimal digits.
[OCELOT Implementation] The OCELOT DBMS that comes with this book defines INT as a 32-bit, signed binary numeric, i.e., INT corresponds to the C long int data type. Thus, INT defines a set of values that are possibly signed whole numbers with a precision of 31 bits and a scale of zero, e.g., -6500 or 476673.
[NON-PORTABLE] INT's radix must be the same as the radix chosen for SMALLINT but is non-standard because the SQL Standard requires implementors to define whether INT and SMALLINT have a binary radix or a decimal radix. [OCELOT Implementation] The OCELOT DBMS that comes with this book defines INT and SMALLINT with a binary radix, i.e., 2. This gives INT a valid range of -2,147,483,647 to +2,147,483,647.
SMALLINT
The required syntax for a SMALLINT <data type> specification is as follows.
SMALLINT <data type> ::=
SMALLINT
SMALLINT defines a set of possibly signed whole numbers that have a scale of zero.
[NON-PORTABLE] SMALLINT's precision must be less than or equal to the precision of INT but is non-standard because the SQL Standard requires implementors to define SMALLINT's precision. FIPS says that SMALLINT should have a precision of at least 4 decimal digits.
[OCELOT Implementation] The OCELOT DBMS that comes with this book defines SMALLINT as a 16-bit signed binary numeric, i.e., SMALLINT corresponds to the C int data type. Thus, SMALLINT defines a set of values that are possibly signed whole numbers with a precision of 15 bits and a scale of zero, e.g., -65 or 476.
[NON-PORTABLE] SMALLINT's radix must be the same as the radix chosen for INT but is non-standard because the SQL Standard requires implementors to define whether SMALLINT and INT have a binary radix or a decimal radix. [OCELOT Implementation] The OCELOT DBMS that comes with this book defines SMALLINT and INT with a binary radix, i.e., 2. This gives SMALLINT a range of -32,767 to +32,767.
NUMERIC
The required syntax for a NUMERIC<data type> specification is as follows.break
NUMERIC <data type> ::=
NUMERIC [ (precision[,scale]) ]
Page 52
NUMERIC is a fixed-point numeric with a decimal precision and decimal scale that are equal to the explicit precision and the explicit scale given; it defines a set of values that are possibly signed decimal numbers with an optionally defined precision and optionally defined scale, e.g., 65.73 or .6 or -819.3 or -.25.
The optional precision, if specified, is an unsigned integer that defines the maximum precision of acceptable values. The minimum precision is 1.
[NON-PORTABLE] The default precision and the maximum precision for NUMERIC are non-standard because the SQL Standard requires implementors to define NUMERIC's default and maximum precisions. Typically, the maximum precision is 15 (the FIPS requirement) but it may be as high as 38 (the DB2 maximum). [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the precision of NUMERIC to range from 1 to 38, with a default precision of 1. For example, this <data type> specification defines a set of values that may range from -9999 to +9999 (4 digits defined):
NUMERIC(4)
and these two equivalent <data type> specifications define a set of values that may range from -9 to +9 (1 digit defined or default):
NUMERIC(1) NUMERIC
The optional scale, if specified, is an unsigned integer, greater than or equal to zero, that defines the maximum number of digits which may follow the decimal point. It must be less than the precision and defaults to zero if omitted. You may define a scale for NUMERIC only if you also define a precision; if no precision is defined, the scale must default to zero.
[NON-PORTABLE] The maximum scale for NUMERIC must always be less than the defined precision but is non-standard because the SQL Standard requires implementors to define NUMERIC's maximum scale. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to define a scale ranging from 1 to 38 for NUMERIC. For example, this
<data type> specification defines a set of values that may range from -999.9 to +999.9 (3 digits before the decimal point and 1 digit after the decimal point, for a total of 4 digits):
NUMERIC(4,1)
DECIMAL
The required syntax for a DECIMAL <data type> specification is as follows.
DECIMAL <data type> ::=
DECIMAL [ (precision[,scale]) ]
DECIMAL may be abbreviated as DEC and is a fixed-point numeric with a decimal scale that is equal to the explicit scale given; it defines a set of values that are possibly signed decimal numbers with an optionally-defined precision and optionally-defined scale, e.g., 65.73 or .6 or -819.3 or -.25.
The optional precision, if specified, is an unsigned integer that defines the maximum precision of acceptable values. DEC's decimal precision must be at least equal to the precision you
define — compare to COBOL, which allows PIC S9(3) COMP-1 but might allot a full-word PIC S9(5) for internal storage. The minimum precision is 1.
[NON-PORTABLE] The default precision, maximum precision, and exact precision for DEC are non-standard because the SQL Standard requires implementors to define DEC'scontinue
Page 53
default, maximum, and exact precisions. Typically, the maximum precision is 15 (the FIPS requirement); it may be as high as 38 (the DB2 maximum). [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the precision of DEC to range from 1 to 38, with a default precision of 1. DEC's decimal precision is equal to the precision you define, i.e., OCELOT treats DEC and NUMERIC as synonyms. For example, this <data type> specification defines a set of values that may range from -9999 to +9999 (4 digits defined):
DEC(4)
and these two equivalent <data type> specifications define a set of values that may range from -9 to +9 (1 digit defined or default):
DEC(1) DECIMAL
The optional scale, if specified, is an unsigned integer, greater than or equal to zero, that defines the maximum number of digits which may follow the decimal point. It must be less than the precision and defaults to zero if omitted. You may define a scale for DEC only if you also define a precision; if no precision is defined, the scale must default to zero.
[NON-PORTABLE] The maximum scale for DEC must always be less than the defined precision but is non-standard because the SQL Standard requires implementors to define DEC's maximum scale. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to define a scale ranging from 1 to 38 for DEC. For example, this <data type> specification defines a set of values that may range from -999.9 to +999.9 (3 digits before the decimal point and 1 digit after the decimal point, for a total of 4 digits):
DEC(4,1)
FLOAT
The required syntax for a FLOAT <data type> specification is as follows.
FLOAT <data type> ::=
FLOAT [ (precision) ]
FLOAT is a floating-point numeric with a binary precision; it defines a set of values that are possibly signed floating-point numbers.
The optional precision, if specified, is an unsigned integer that defines the maximum number of bits (including the hidden bit) in the mantissa. FLOAT's binary precision must be at least equal to the precision you define. The minimum precision is 1.
[NON-PORTABLE] The default precision, maximum precision, and binary precision for FLOAT are non-standard because the SQL Standard requires implementors to define FLOAT's default, maximum, and exact precisions. FIPS says that FLOAT should have a binary precision of at least 20. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows the precision of FLOAT to range from 1 to 53, with a default precision of 53. Thus, FLOAT defines a set of values that are possibly signed floating-point numbers with this format:
[sign]+digit+period+ up to 14 digits+E+[sign]+ up to 3 digits
For example, -1.27982E+015 or .465E-007. The IEEE Standard for Binary Floating-Point Arithmetic (IEEE Standard 754-1985) specifies two usual mantissa sizes: 24 and 53. OCELOT supports both; regardless of the actual precision specified for FLOAT, there are really only two possible results. If you define FLOAT with a precision that is less than or equal to 24, thecontinue
Page 54
actual binary precision will equal 24 bits in the mantissa. For example, these two <data type>
specifications are equivalent; they both define a set of floating-point values whose mantissa may range up to a precision of 24 bits:
FLOAT(12) FLOAT(24)
If you define FLOAT with a precision between 25 and 53, the actual binary precision will equal 53 bits in the mantissa. For example, these three <data type> specifications are equivalent; they all define a set of floating-point values whose mantissa may range up to a precision of 53 bits:
FLOAT FLOAT(27) FLOAT(53)
[NON-PORTABLE] The minimum exponent and the maximum exponent for FLOAT values are non-standard because the SQL Standard requires implementors to define FLOAT's minimum and maximum exponents. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to use numbers with exponents ranging from -038 to +038 for FLOAT.
REAL
The required syntax for a REAL <data type> specification is as follows.
REAL <data type> ::=
REAL
REAL is a floating-point numeric with a binary precision, i.e., REAL defines a set of values that are possibly signed floating-point numbers.
[NON-PORTABLE] The binary precision of REAL must be less than the precision defined for DOUBLE PRECISION but is non-standard because the SQL Standard requires implementors to define REAL's exact precision. [OCELOT Implementation] The OCELOT DBMS that comes with this book treats REAL as equivalent to FLOAT(24). Thus, REAL defines a set of values that are possibly signed floating-point numbers with this format:
[sign]+digit+period+up to 6 digits+E+[sign]+ up to 2 digits
For example, -1.27982E+15 or .465E-07.
[NON-PORTABLE] The minimum exponent and the maximum exponent for REAL values are non-standard because the SQL Standard requires implementors to define REAL's minimum and maximum exponents. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to use numbers with exponents ranging from -38 to +38 for REAL.
DOUBLE PRECISION
The required syntax for a DOUBLE PRECISION <data type> specification is as follows.
DOUBLE PRECISION <data type> ::=
DOUBLE PRECISION
DOUBLE PRECISION is a floating-point numeric with a binary precision, i.e., DOUBLE PRECISION defines a set of values that are possibly signed floating-point numbers.break
Page 55
[NON-PORTABLE] The binary precision of DOUBLE PRECISION must be greater than the precision defined for REAL but is non-standard because the SQL Standard requires implementors to define DOUBLE PRECISION's exact precision. FIPS says that DOUBLE PRECISION should have a binary precision of at least 30. [OCELOT Implementation] The OCELOT DBMS that comes with this book treats DOUBLE PRECISION as equivalent to FLOAT(53). Thus, DOUBLE PRECISION defines a set of values that are possibly signed floating-point numbers
with this format:
[sign]+digit+period+up to 14 digits+E+[sign]+up to 3 digits
For example, -1.27982E+015 or .465E-007.
[NON-PORTABLE] The minimum exponent and the maximum exponent for DOUBLE
PRECISION values are non-standard because the SQL Standard requires implementors to define DOUBLE PRECISION's minimum and maximum exponents. [OCELOT Implementation] The OCELOT DBMS that comes with this book allows you to use numbers with exponents ranging from -038 to +038 for DOUBLE PRECISION.
WARNING: Throughout this section, we've shown examples of <numeric literal>s that conform to the various SQL <data type>s we're describing. It is important to remember that your DBMS doesn't see them that way. To an SQL DBMS, <exact numeric literal>s have a DECIMAL <data type> and
<approximate numeric literal>s have a FLOAT <data type>.
Now that we've described SQL's numeric <data type>s, let's look at some example SQL statements that put them to use.
These SQL statements make a Table with four exact numeric Columns, insert a row, then search for any number less than -1:break
CREATE TABLE Exact_Examples (
occurrence_decimal DECIMAL(5), occurrence_numeric NUMERIC(7,2), occurrence_integer INTEGER, occurrence_smallint SMALLINT);
INSERT INTO Exact_Examples ( occurrence_decimal, occurrence_numeric, occurrence_integer, occurrence_smallint)
VALUES (12345, 12345, 12345, 12345);
SELECT occurrence_decimal, occurrence_numeric, occurrence_integer, occurrence_smallint FROM Exact_Examples
WHERE occurrence_decimal < -1;
Page 56
These SQL statements make a Table with three approximate numeric Columns, insert a row, then search for any number less than 50,000:
CREATE TABLE Approximate_Examples ( occurrence_float FLOAT(53), occurrence_real REAL,
occurrence_double DOUBLE PRECISION);
INSERT INTO Approximate_Examples (
According to the IEEE Standard for Binary Floating-Point Arithmetic, ''single-" and
"double-precision" numbers are defined as follows.
PRECISION
Double 1 11 53 -304 to +308 15 digits
* The most significant mantissa bit is assumed to be 1. It is not stored.
You'd find the same specification in, say, an Intel FPU reference text or a C++ manual. But we found discrepancies when looking through documents for Java (where the exponent range is between -35 and +38), Delphi (where the exponent range is between -45 and +38 for
single-precision and between -324 and +308 for double-precision), and FIPS SQL (where the FLOAT exponent+size are 9+47 and the REAL exponent+size are 7+23). So for portability reasons, it would be a good idea to avoid the extremes of the IEEE range.
Most DBMSs support IEEE float formats because FIPS requires that the decimal ranges be supported and because the DBMS code itself is written in a language that supports IEEE floats.
But never does an official SQL standard tell vendors "how to store the data." So it might be that your DBMS actually uses the IEEE sizes or it might be that your DBMS actually stores float decimal literals (as xBase does) and processes with base-10 arithmetic. If so, the following information doesn't apply to you.
[Obscure Information] applies for the rest of this section.