What is the max value of numeric(19, 0) and what happens if it is reached?

Question

In an SQL Server table, I have a column with the following type:

numeric(19, 0)

What is the max value for this type?

This corresponds to a Long in Java, which has a maximum value of 9,223,372,036,854,775,807 (19 digits).

Does the above type in SQL Server have the same max value?

What happens in SQL Server if the above value is reached?

Answer 1

The MSDN documentation rather unhelpfully says:

When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1...Maximum storage sizes vary, based on the precision.

(max precision is numeric(38)).

From experimenting in SQL Server 2012, the maximum value for numeric(19,0) is 10e18 - 1025. Attempting to insert any higher value gives:

Msg 8115, Level 16, State 6, Line xx Arithmetic overflow error converting float to data type numeric.

The maximum does vary by precision and appears to be a bit under 10^(p-1) (where p is precision), though the exact relationship is not obvious. No doubt a more exact answer could be given by delving into the bit-level details of the storage method.

Answer 2

numeric(19,0) specifies a fixed precision number having a precision of 19 digits and 0 digits to the right of the decimal point. It should not overflow. See decimal and numeric for more information.