Arithmetic calculations and decimals

Question

Please have a look at the code below and explain to me why there is a deviance in the final results. Note that the difference is the introduction of the brackets in the second calculation. Thanks!

Code:

DECLARE  @A decimal(38,19) = 7958011.98
DECLARE  @B decimal(38,19) = 10409029441
DECLARE  @C decimal(38,19) = 10000000000

DECLARE  @Z1 decimal(38,19)
DECLARE  @Z2 decimal(38,19)


SET @Z1 = @A * @B / @C
SET @Z2 = @A * (@B / @C)

SELECT  @Z1 AS [Correct], 
        @Z2 AS [Wrong]

Results:

Correct = 8283518.0991650000000000000
Wrong   = 8283510.5860060000000000000

Possible duplicate of [T-SQL Decimal Division Accuracy](http://stackoverflow.com/questions/423925/t-sql-decimal-division-accuracy). This is very well documented on MSDN: http://msdn.microsoft.com/en-us/library/ms190476.aspx. — gbn, Jan 07 '13 at 13:27
@Oded: I don't follow. Mathematically, they are the same. Furthermore, (B / C) has less than 19 decimal digits, so that shouldn't be an issue either. The answer to this question is not obvious. — Daniel Hilgarth, Jan 07 '13 at 13:31
@DanielHilgarth - Not saying it is obvious, but that order of execution is what's going on here. — Oded, Jan 07 '13 at 13:32
@Oded: Sure. But knowing that still doesn't answer the question :-) — Daniel Hilgarth, Jan 07 '13 at 13:33

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

The intermediate datatypes are different because of this MSDN article

That is, (@B / @C) evaluated first, follows rules like this. The intermediate datatype then affects the multiplication by @A

You can see the intermediate and final types here (before assigning to a decimal(38,19) type

SELECT
     @A * @B,        -- decimal (x, 6)
     @A * @B / @C,   -- decimal (x, 6)
     (@B / @C),      -- decimal (x, 6)
     @A * (@B / @C)  -- decimal (x, 6)

So, instead of 1.0409029441 you get 1.040902 for your 2nd math

Note, your 1st is wrong too. It is actually 8283518.099165070318

edited May 23 '17 at 11:50

Community

1
1

answered Jan 07 '13 at 13:30

gbn

422,506
82
585
676

What would be the intermediate datatype in this concrete case? If I understand the MSDN article correctly, it should be something like `DECIMAL(96, 58)`, way enought to hold the result. – Daniel Hilgarth Jan 07 '13 at 13:35
Thanks. How do you reach these values? the scale for divisions is `max(6, s1 + p2 + 1)` - in our case `max(6, 38 + 19 + 1) => max(6, 58) => 58` – Daniel Hilgarth Jan 07 '13 at 13:38
@DanielHilgarth: (96,58) will be scaled back to (38,0). However, SQL Server seems to ensure a *minimum* scale of 6. Will look for reference, but this is implied by the max(6,...) bit. Edit: http://blogs.msdn.com/b/sqlprogrammability/archive/2006/03/29/564110.aspx – gbn Jan 07 '13 at 13:49
Ah, so when the scale or precision is above the allowed maximum, it won't be scaled back to the allowed maximum but to zero (or 6, via the `max`)? Pretty counterintuitive. – Daniel Hilgarth Jan 07 '13 at 13:51
precision is is scaled back to the max (38) and scale by the same amount. However, scale always has a minimum of 6... See my link http://blogs.msdn.com/b/sqlprogrammability/archive/2006/03/29/564110.aspx – gbn Jan 07 '13 at 13:54
@gbn: For this example, what datatypes would you recommend and did you compute this exact value (8283518.099165070318) using SQL? – Mattkoen Jan 07 '13 at 15:13
@gbn still looking for an answer here. Thanks – Mattkoen Jan 08 '13 at 07:29
@Mattkoen: frankly, I wouldn't do this in SQL Server if I needed 100% accuracy: I'd send to the client and use the # or Java (etc) maths stuff. – gbn Jan 08 '13 at 07:43

Arithmetic calculations and decimals

1 Answers1