15.10 - NUMBER Data Type - Teradata Database

Teradata Database SQL Data Types and Literals

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1143-151K

Represents a numeric value with optional precision and scale limitations.

where:

 

Syntax element …

Specifies …

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” on page 17 and “Constraint Attributes” on page 30 for specific information.

 

In order to...

Use the following syntax...

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 n specifies the precision and m specifies the scale. The value of n can range from 1to 38. The scale can range from 0 to n.

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

    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 n total digits with m fractional digits.

    NUMBER(*,m)

    0 to 18, based on the actual value stored.

    Number with up to m fractional 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.

    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” on page 281 and “FORMAT Phrase and NUMERIC Formats” on page 300.

    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.

    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. For details, see “Storage” on page 117.

    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 SQL Data Definition Language.

    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 DECIMAL/NUMERIC Data Types” on page 123.

    Rounding mode is controlled by the RoundNumberAsDec field in DBS Control. For details, see “Rounding NUMBER Data Type” on page 124.

    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.

    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. For details, see “Storage” on page 117.

    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.

    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.
  • Note: 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.

    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

     

    FOR more information on …

    SEE …

    rounding NUMBER types

    “Rounding” on page 123.

    the result of expressions containing NUMBER arguments

    SQL Functions, Operators, Expressions, and Predicates.

    the default format for NUMBER and using the FORMAT phrase with NUMBER data

    “Data Type Default Formats” on page 281 and “FORMAT Phrase and NUMERIC Formats” on page 300.