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.