NUMBER Data Type

Teradata Vantage™ Data Types and Literals

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

Purpose

Represents a numeric value with optional precision and scale limitations.

Syntax



n
The precision.
The range is from 1 to 38.
m
The scale. This indicates the maximum number of digits allowed to the right of the decimal point.
If * is specified, the range of m is from 0 to 38.
If n is specified, the range of m is from 0 to n.
attributes
Appropriate data type attributes, column storage attributes, or column constraint attributes.
See Core Data Type Attributes and Constraint Attributes for specific information.
indicate NUMBER with the system limits for precision and scale
NUMBER, or equivalently NUMBER (*)
limit the scale
NUMBER (*,m)
where m specifies a scale in the range from 0 to 38.
limit the precision and scale
  • NUMBER (n,m)
  • NUMBER (n), which is equivalent to NUMBER (n,0)
where nspecifies the precision and m specifies the scale. The value of n can range from 1 to 38. The scale can range from 0 to n.

ANSI Compliance

NUMBER is a Teradata extension to the ANSI SQL:2011 standard.

Storage

NUMBER is stored as a variable-length value comprised of an exponent and mantissa. The base of the exponent is 10; therefore, common decimal numbers can be represented exactly.

With one exception, the storage layout is as follows:

1 Byte 1 - 17 Bytes
Exponent Mantissa

The exception is the value zero, which is indicated by a zero-length NUMBER field with no exponent or mantissa.

The following table summarizes the storage needs and range of NUMBER values.

Data Type Database Storage Required (in bytes) Range of Numbers
NUMBER(n,m) 0 to 18, based on the actual value stored. Number of ntotal digits with mfractional digits.
NUMBER(*,m) 0 to 18, based on the actual value stored. Number with up to mfractional digits.
NUMBER(*) 0 to 18, based on the actual value stored. ± [1E-130 to 9.99…9E125], including 0

If a COMPRESS clause is specified, the value is stored in the compressed portion of the row and an extra byte is required to indicate the length. If no COMPRESS clause is present, the value is stored in the variable portion of the row, which requires two bytes. For every row that has a variable portion, two bytes are required.

External Representation of NUMBER

In Field mode, NUMBER data is returned based on the FORMAT phrase. For information about the default format for NUMBER and using the FORMAT phrase with NUMBER data, see Data Type Default Formats and FORMAT Phrase and NUMERIC Formats.

For response modes other than Field mode, NUMBER data has the following representation for most values.

1 byte Length 2 bytes Scale 1 to 17 bytes two’s complement representation of the unscaled value, in the client-appropriate endianness.

When NUMBER is zero or null, a length value of zero indicates no scale or unscaled value is required.

Differences Between NUMBER and DECIMAL

NUMBER (n,m) is similar to DECIMAL (n,m) in functionality and can be used wherever DECIMAL is supported.

NUMBER differs from DECIMAL in the following ways:

DECIMAL NUMBER
The data type is fixed-length with 1, 2, 4, 8 or 16 bytes, depending on the precision. The data type is variable-length and varies from 0 to 18 bytes internally and from 1 to 20 bytes externally.
Numbers are scaled by the power of ten equal to the number of fractional digits. The number is stored as a two’s complement binary number. NUMBER is stored as a variable-length value comprised of an exponent and mantissa.
You cannot change the precision or scale of an existing DECIMAL column without modifying data rows. You can increase the precision or scale of an existing NUMBER column without modifying data rows. However, some restrictions apply. For details, see “ALTER TABLE” in Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.
The intermediate results of DECIMAL arithmetic have a precision of 15, 18, or 38 depending on the arguments used in the arithmetic expression. Calculations for the NUMBER data type are guaranteed to 38 digits of precision, but are often performed with 39 or 40 digits of precision. If precision and scale are not limited, NUMBER data will retain the full precision and scale of the calculations.

Therefore, the arithmetic operation 1./3. returns 0. when performed with DECIMAL, but returns .3333333333... when performed with NUMBER.

Rounding mode is controlled by the RoundHalfWayMagUp field in DBS Control. For details, see Rounding. Rounding mode is controlled by the RoundNumberAsDec field in DBS Control. For details, see Rounding.
DECIMAL when specified without precision or scale defaults to DECIMAL(5,0). NUMBER when specified without precision or scale defaults to NUMBER with the system limits for precision and scale.

Differences Between NUMBER and FLOAT

Numeric values can be represented as:
  • NUMBER(*,m)
  • NUMBER(*), which is equivalent to NUMBER

NUMBER can be used wherever FLOAT is supported. However, NUMBER differs from FLOAT in the following ways:

FLOAT NUMBER
The data type is fixed-length and uses 8 bytes of storage. The data type is variable-length and varies from 0 to 18 bytes internally and from 1 to 20 bytes externally.
FLOAT is stored using the IEEE-754 standard, which represents floating point numbers using base-2 and is generally known as binary float.

This storage scheme cannot represent all decimal values exactly.

NUMBER is stored as a variable-length value comprised of an exponent and mantissa.

This storage scheme stores decimal values exactly because it uses a decimal base.

FLOAT can only provide accuracy of 15 decimal digits. Calculations for the NUMBER data type are guaranteed to 38 digits of precision, but are often performed with 39 or 40 digits of precision. If precision and scale are not limited, NUMBER data will retain the full precision and scale of the calculations.

Usage Notes

The NUMBER data type provides the following benefits:
  • Increased flexibility in defining numeric columns by providing the ability to increase the precision or scale of existing NUMBER columns in tables without modifying the data rows. However, some restrictions apply.
  • Increased efficiency in storing numeric data because NUMBER is a variable-length data type that can vary from 0 to 18 bytes, depending on the value stored.
  • Increased flexibility in computation compared with the DECIMAL data type because the result is not limited by the precision or scale of the input.
  • Greater range than the DECIMAL data type.
  • Greater accuracy than the FLOAT data type because NUMBER has greater guaranteed precision and NUMBER can represent common decimals exactly.
  • Increased compatibility with other databases. Several database vendors include a similar NUMBER data type.
For many purposes, the NUMBER data type can provide exact results. However, it is a floating point type. Any database vendor supporting floating point types is subject to different answers based on the order of evaluation. There will be cases where Teradata Database will not produce the same results as other vendors or even the same answer on different executions of the same query.

Example: NUMBER Data Type

Consider the following table definition:

CREATE TABLE num_tab
  (n1 NUMBER(*,3),
   n2 NUMBER,
   n3 NUMBER(*),
   n4 NUMBER(5,1),
   n5 NUMBER(3) );

The following table shows the result of inserting various values into a column defined as NUMBER (3).

If you insert this value... the value of column n5 will be...
1.234 1
123.6789 124
1234.56 An error is returned since the inserted value exceeds the size of column n5.

The following table shows the result of inserting various values into a column defined as NUMBER (*,3).

If you insert this value... the value of column n1 will be...
1.234 1.234
1234.6789 1234.679

Related Topics

FOR more information on … SEE …
rounding NUMBER types Rounding.
the result of expressions containing NUMBER arguments Teradata Vantage™ SQL Functions, Expressions, and Predicates, B035-1145.
the default format for NUMBER and using the FORMAT phrase with NUMBER data Data Type Default Formats and FORMAT Phrase and NUMERIC Formats.