Dealing with decimal precision

Question

We have a number of fields defined as numeric(38,20) – because the data travels to an ORACLE db with that scale/precision.

When performing calculations, rounding tends to happen between 12 and 14 decimal places (depending). I tested various combinations of float/decimals in hopes of keeping precision and store back into numeric(38,20)

e.g.

enter code here

declare @dtest20 decimal(38,20) = cast( 1.44444444441234567892 as decimal(38, 20))
declare @ftest20 float = cast( 1.44444444441234567892 as float) 
declare @f100 float = cast( 100.0 as float ) 
declare @d100 decimal(38,20) = cast( 100.0 as decimal(38,20))

select @dtest20 * @d100 as 'dtest20 x d100'
, cast( @dtest20 * @d100 as decimal(38,20)) as 'dtest20 x d100' 
, @ftest20 * @f100 as 'ftest20 x f100' 
, cast( @ftest20 * @f100 as decimal(38,20)) as 'ftest20 x f100' 

dtest20 x d100 dtest20 x d100 ftest20 x f100 ftest20 x f100 144.444444 144.44444400000000000000 144.444444441235 144.44444444123457000000

I know this is really an MS-SQL thing, but is there any work around/recommendation to perform calculations on large precision numbers and avoid inadvertent rounding?

Thanks!

SQL is not the right platform to do perform precise calculations, but as a a rule of thumb you should store values as decimals, but perform calculations using floats. — Alex, Apr 23 '15 at 16:01
@JacodeGroot - "...store values as decimals, but perform calculations using floats" - I really have to question why you want to store results in a DECIMAL, then throw away precision and accuracy by using FLOATs in calculations? (͏the͏ a͡nći͜e͡nt ̷evìl͢ ҉a͝w̴a̸k͢en̵s͞.͜..̡) This is madness! (́h̷ę̛ ̶͝c̵̸o͝m҉̸͜e̷͝͠s..͝.͝)͠͡ Turn away! Turn back!! C̢͡҉͏t̨̨̕h̷̛́͜͡u҉̡̀͝͠l̛̛̀͜͞h̶̨͞ú̴̶͟.̢̨̛͘͠.̵̕͜!̢͟͜͟ Flee from the unnameable terror in the FPU!!! Į́́á̵̧!̴̧̕ ̶̛̀Į̵̵̵a̡͜͠!͡͡ ̡̢̛͘C̷̨̡͞t͏͝҉҉́h͜u̷̶l̸h́̕͞ù͘͟͠!̸́!̵̧̛̀́ ҉̕͢͞҉C̵̡͘͢͞t͝҉͜h͟u̵͘͡l̢̡̀h̡̀͢͜͡ư̶̵ ̵͟͟f̷̶̀͞͝h̸̛͜͠҉ţ̷a̸̛̛͞g̸̨̀n͏҉̸̨!̀҉!̵!́͡͡ — Bob Jarvis - Слава Україні, Apr 23 '15 at 17:01
@BlobJarvis, one problem with decimals is that values get truncated and that can result in drifts in certain calculations. Also see this post for example:http://stackoverflow.com/questions/14313598/why-precision-is-decreasing-when-multiply-sum-to-other-number — Alex, Apr 23 '15 at 17:19

Mike Sherrill 'Cat Recall' · Answer 1 · 2015-04-24T09:44:22.957

The rule that must be obeyed: Use exact numeric data types for all values. Don't exceed your precision and scale. (PostgreSQL syntax follows.)

select cast( 1.44444444441234567892 as decimal(38, 20)) as cast_decimal;

cast_decimal
numeric(38,20)
--
1.44444444441234567892

Double precision floating point numbers on my machine have only about 15 decimal digits of precision. In the case below, the value we're given won't fit. The dbms returns the closest floating-point value.

select cast( 1.44444444441234567892 as float) as cast_float;

cast_float
double precision
--
1.44444444441235

Multiplying a decimal by an integer returns a decimal. So mixing decimal and integer data types in multiplication gives unsurprising results. That's what we want--unsurprising results.

select cast( 1.44444444441234567892 as decimal(38, 20)) * 100 as dec_x_int;

dec_x_int
numeric
--
144.44444444123456789200

Dividing a decimal by an integer gives unsurprising results. But this division lost a digit, because the number of digits in the actual result is more than 20. That shouldn't be a surprise.

select cast( 1.44444444441234567892 as decimal(38, 20)) / 100 as dec_div_int;

dec_div_int
numeric
--
0.01444444444412345679

Multiplying a decimal by a float returns an implementation-defined approximate numeric. (See below.) We've lost precision by not following the rule that must be obeyed.

select cast( 1.44444444441234567892 as numeric(38,20)) * 100::float as dec_x_flt;

dec_x_flt
double precision
--
144.444444441235

SQL Standards

SQL standards distinguish exact numerics from approximate numerics. What happens with the least significant digits (rounding, truncating) is implementation-defined. The precision of the results of multiplication and division are implementation-defined.

Mixing an exact numeric with an approximate numeric in an expression returns an implementation-defined approximate numeric. So, mix a decimal with a float, and you get back either a float or a double.

Dealing with decimal precision

1 Answers1