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 
The value is expressed as a two's complement scaled binary number, with littleendian 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, littleendian on Intel and bigendian on SPARC).
The external representation on mainframe clients (for example, IBM mainframe) is packed decimal. Packed decimal is expressed in signmagnitude form. The rightmost nibble is the sign (the bolded entries show the preferred representation):
Sign  Hexadecimal Values 

+ 
X'A' X'C' X'E' X'F' 
 
X'B' X'D' 
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 16byte 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 DBAspecified 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(nm,kj))),max(m,j)) 
* 
DECIMAL(min(p,n+k),(m+j)) 

/,MOD 
DECIMAL(p,max(m,j)) 
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 leftmostmax(m,j)
) plus the whole digits (max(nm,kj)
), plus one for a possible carry. For example, adding twoDECIMAL(5,2)
values will result in aDECIMAL(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 twoDECIMAL(5,2)
values will result in aDECIMAL(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 oneDECIMAL(5,2)
value by another will result in aDECIMAL(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 
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)
andDECIMAL(18)
both take8
bytes, while anything larger thanDECIMAL(18)
takes16
bytes. A client application must be able to deal with the difference when retrievingDECIMAL(n)
data whenn > 18
. UsingMaxDecimal 18
can reduce the immediate need for client application changes (only applications dealing whenDECIMAL(n), n > 18
, need to be aware of the difference). 
On mainframe clients,
DECIMAL(15)
takes 8 bytes,DECIMAL(18)
takes 10 bytes, andDECIMAL(38)
takes 20 bytes. It was important when the maximum went fromDECIMAL(15)
toDECIMAL(18)
that client applications didn't have to change immediately (as long as the operands involved had noDECIMAL(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 requestbyrequest 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)) 
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 CLIv2based applications. A DBCAREA
field accepts the setting, and CLIv2 copies the setting into the Options parcel for communication with the Teradata database.
Some Teradata utilities that export data provide access to the Maximum DECIMAL Precision setting:

In BTEQ, the
SET DECIMALDIGITS
command can be used. 
In FastExport, the
DECIMALDIGITS
option on theBEGIN 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, B0351143
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, B0351145
Additional information on MaxDecimal and the DBSControl utility may be found in:
 Teradata Database Utilities  Volume 1 AK, B0351102
Additional information on access to Maximum DECIMAL Precision via CLIv2 may be found in:
 Teradata CallLevel Interface Version 2 Reference for ChannelAttached Systems, B0352417
 Teradata CallLevel Interface Version 2 Reference for NetworkAttached Systems, B0352418
Additional information on the Teradata utilities that support Maximum DECIMAL Precision may be found in:
 Basic Teradata Query Reference, B0352414
 Teradata FastExport Reference, B0352410
 Teradata Parallel Transporter Reference, B0352436
 Teradata Parallel Transporter User Guide, B0352445