NUMERIC (TSQL Datatype)

Is a 16-bytes decimal number (base 10) with fixed precision and scale.

Syntax

NUMERIC [( precision [, scale] )]

Range

precision

is the maximum total number of decimal digits, both at the left and at the right of the decimal point. Max precision is 34.

• When precision is not specified in declarations, the default is 18.
• When precision is not specified inside a CAST function, the default is also 18.

scale

is the number of decimal digits at the right of the decimal point.
If scale is not specified, the default is 0.

Remarks

The datatype numeric is the main type used in RSQL for decimal numbers.

During arithmetic calculations, the numeric results are created with precision and scale that store the value with maximum accuracy.

Literal Numeric

Any literal number containing a decimal point (but no exponent) is parsed as a numeric type.

With RSQL, the suffix d forces any literal number to be parsed as numeric.

1234.56
20.
-678.125

123d         -- RSQL only. Parsed as numeric literal.
123.50e3d    -- RSQL only. Parsed as numeric literal.

Examples

DECLARE @a NUMERIC       = 123.89; -- @a is NUMERIC(18, 0)
DECLARE @b NUMERIC(6, 2) = 123.89; -- @b can contain numbers from -9999.99 to 9999.99

PRINT @a;
PRINT @b;
PRINT @a / 3 * @b;
PRINT 123.45 + 20. * 1.67; -- all these constants are 'numeric', because they contain a decimal point

The result is:

124
123.89
5120.786666666625
156.85

Accuracy

All operations on numeric types store the result in a numeric with the highest accuracy. This way, results of arithmetic calculation with numeric are of the same quality as if floating point were used, with the benefit of base 10 calculations.

The result is even more accurate than with money, because intermediate results lose precision as they are coerced to money.

Examples

PRINT $123.0 / $3 + $15.789 * $1.034;           -- calculation is done with 'money'   values (base 10)
PRINT  123.0 /  3 +  15.789 *  1.034;           -- calculation is done with 'numeric' values (base 10)
PRINT  123.0e0 /  3e0 +  15.789e0 *  1.034e0;   -- calculation is done with 'float'   values (base  2)

The result is:

57.3258           <--- result with 'money'
57.325826000000   <--- result with 'numeric' is more accurate than 'money'
57.325826         <--- result with 'float'

So, it is better to store currency value in numeric datatype instead of money, as calculation accuracy is better.