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.