## How many digits in a DECIMAL?

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.

## How many digits in a DECIMAL?

Teradata currently supports up to 38 digits in `DECIMAL` columns. There are several ways to control how many digits you're working with, and they interact in various ways.

## DECIMAL(n,m)

The most obvious way to specify the number of digits desired is with the data type clause, `DECIMAL(n,m)`, where `1<=n<=38` and `0<=m<=n`. This is the way columns are defined, and also the way input sent from a client to the Teradata database is defined (for example, via the `USING` modifier). `DECIMAL` columns are stored by the Teradata database in 1, 2, 4, 8, or 16 bytes, depending on the value of `n`:

Number of Digits Number of Bytes
1 to 2 1
3 to 4 2
5 to 9 4
10 to 18 8
19 to 38 16

Bytes used to store DECIMAL columns

The value is expressed as a two's complement scaled binary number, with little-endian byte order.

The external representation on network clients is the same as the internal representation. Byte order is appropriate for the client platform (for example, little-endian on Intel and big-endian on SPARC).

The external representation on mainframe clients (for example, IBM mainframe) is packed decimal. Packed decimal is expressed in sign-magnitude form. The rightmost nibble is the sign (the bolded entries show the preferred representation):

`+` `X'A' X'C' X'E' X'F'`
`-` `X'B' X'D'`

Packed Decimal Sign Values

The other nibbles each contain one decimal digit, represented by `X'0'` through `X'9'` (if "`n`" is even, the leftmost nibble is padded with `X'0'`). The total size of the packed decimal value is `(n+2)/2` bytes, for a maximum of 20 bytes. IBM hardware supports packed decimal operations on up to 16-byte packed decimal values (31 digits).

## MaxDecimal

Teradata defines the resulting data type for arithmetic and other operations, based on the data types of the participating operands.

If `DECIMAL` operand(s) are present in an expression, a DBA-specified value, the `DBSControl` setting `MaxDecimal`, becomes important in determining the precision and scale of the expression result. `MaxDecimal` specifies the maximum precision of the `DECIMAL` result of an expression. The valid values for `MaxDecimal` are `0, 15, 18`, and `38` (`0` is the default and is treated the same as `15`).

Let's use arithmetic operations on two `DECIMAL` operands, which will always generate a `DECIMAL` result, as an example:

Left Operand Right Operand Operator Result Data Type

`DECIMAL(n,m)` `DECIMAL(k,j)` `+,-` `DECIMAL(min(p,(1+max(m,j)+max(n-m,k-j))),max(m,j))`
`*` `DECIMAL(min(p,n+k),(m+j))`
`/,MOD` `DECIMAL(p,max(m,j))`

Result of Arithmetic Operations on Two DECIMAL Operands

Those formulas can look complex, so let's boil it down, ignoring p for the moment:

• For addition and subtraction, we need to have enough digits to the right of the decimal point to hold all of the fractional digits of either operand (the rightmost `max(m,j)`). We also need to have enough digits total to hold the fractional digits (the leftmost `max(m,j)`) plus the whole digits (`max(n-m,k-j)`), plus one for a possible carry. For example, adding two `DECIMAL(5,2)` values will result in a `DECIMAL(6,2)` value.
• For multiplication, we need enough total digits to hold the product `(n+k)` and enough digits to the right of the decimal point to hold all of the fractional digits (m+j). For example, multiplying two `DECIMAL(5,2) `values will result in a `DECIMAL(10,4) `value.
• For division and modulo, we need as many digits as we can get for the quotient (`p`), retaining as many fractional digits as either operand had (`max(m,j)`). For example, dividing one `DECIMAL(5,2)` value by another will result in a `DECIMAL(p,2) `value.

The intent of the above is to avoid overflows whenever possible, while maintaining maximum precision.

Now, let's reintroduce p:

MaxDecimal n and k p

`0 or 15` `n and k <= 15` `15`
`(n or k > 15) and (n and k <= 18)` `18`
`n or k > 18` `38`
`18` `n and k <= 18` `18`
`n or k > 18` `38`
`38` `m and k = any value` `38`

Value of p

Based on the `MaxDecimal` setting, `p` essentially limits the total number of digits in the result.

MaxDecimal was introduced when the maximum number of digits allowed in `DECIMAL` values was increased from `15` to `18`. It's purpose (assuming the default setting was used) was to maintain backward compatibility by avoiding a change in the result data type when the operands were no larger than `DECIMAL(15)`. When the maximum number of digits allowed in `DECIMAL` values was further increased from `18` to `38`, `MaxDecimal` was extended, to achieve the equivalent backward compatibility when the operands were no larger than `DECIMAL(18)`.

Limiting the total number of digits becomes important when client representation is taken into account:

• On network clients (and in the Teradata database), `DECIMAL(15)` and `DECIMAL(18)` both take `8` bytes, while anything larger than `DECIMAL(18)` takes `16` bytes. A client application must be able to deal with the difference when retrieving `DECIMAL(n)` data when `n > 18`. Using `MaxDecimal 18` can reduce the immediate need for client application changes (only applications dealing when `DECIMAL(n), n > 18`, need to be aware of the difference).
• On mainframe clients, `DECIMAL(15)` takes 8 bytes, `DECIMAL(18)` takes 10 bytes, and `DECIMAL(38)` takes 20 bytes. It was important when the maximum went from `DECIMAL(15)` to `DECIMAL(18)` that client applications didn't have to change immediately (as long as the operands involved had no `DECIMAL(n), n > 15`).

Note that `MaxDecimal` only affects the result data type of expressions, nothing else. It is possible to load data into the Teradata database with as many as 38 decimal digits, regardless of the `MaxDecimal` setting. Similarly, it is possible to extract `DECIMAL` column data (no operations involved) from the Teradata database with as many as 38 decimal digits, regardless of the `MaxDecimal` setting. Depending on the client application and platform, this can lead to problems, as we'll see next.

## Maximum DECIMAL Precision

On a request-by-request basis, a client application can specify the maximum `DECIMAL` precision it is prepared to accept in results. This setting is independent of the `DBSControl` `MaxDecimal` setting, and can take values ranging from 0 to 38 (a value of 0 requests the default, 18 digits). The setting operates as an implicit `CAST` of `DECIMAL` results:

Maximum DECIMAL Precision (q) DECIMAL(n,m)

`q >= n` `DECIMAL(n,m)`
`q < n` `CAST to DECIMAL(q,min(q,m))`

Maximum DECIMAL Precision and DECIMAL Results

If an overflow occurs as a result of the implicit `CAST`, a `2616` error (numeric overflow) is returned.

Mainframe client applications might typically set Maximum DECIMAL Precision to 31, since 31 digits (16 bytes) is the maximum precision supported by the packed decimal hardware operations.

Note that Maximum DECIMAL Precision only affects the data type of `DECIMAL(n)` results, when `n > q.` It has no effect on the data type of DECIMAL data sent to the Teradata database. Also, Maximum DECIMAL Precision has no effect on results in field mode, since in field mode the `DECIMAL` data will have been formatted as a displayable character string using the appropriate `FORMAT` information.

Maximum DECIMAL Precision settings are available to all CLIv2-based applications. A `DBCAREA` field accepts the setting, and CLIv2 copies the setting into the Options parcel for communication with the Teradata database.

• In BTEQ, the `SET DECIMALDIGITS` command can be used.
• In FastExport, the `DECIMALDIGITS` option on the `BEGIN EXPORT` command can be used.
• In Teradata Parallel Transporter (Export and Selector Operators), the `MaxDecimalDigits = q` attribute can be used.

## References

Additional information on the DECIMAL data type, including internal and external representation, may be found in:

• Teradata Database SQL Data Types and Literals, B035-1143

Additional information on the data types of expression results, as well as data type conversions, may be found in:

• Teradata Database SQL Functions, Operators, Expressions, and Predicates, B035-1145

Additional information on MaxDecimal and the DBSControl utility may be found in:

• Teradata Database Utilities - Volume 1 A-K, B035-1102

• Teradata Call-Level Interface Version 2 Reference for Channel-Attached Systems, B035-2417
• Teradata Call-Level Interface Version 2 Reference for Network-Attached Systems, B035-2418

Additional information on the Teradata utilities that support Maximum DECIMAL Precision may be found in:

• Basic Teradata Query Reference, B035-2414
• Teradata Parallel Transporter Reference, B035-2436
• Teradata Parallel Transporter User Guide, B035-2445
Tags (4)
2 REPLIES
Enthusiast

## Re: How many digits in a DECIMAL?

Thanks for the post.
A general question -
Can you highlight increase in maximum decimal digits for each Teradata release?