17.10 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)

General

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 Vantage will not produce the same results as other vendors or even the same answer on different executions of the same query.

Storage

NUMBER is stored as a variable-length value comprising 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.

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 comprising 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 more information, 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 comprising 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.