0

Just wondering about SQL Server Money data type:

------- SCRIPT 1
declare 
  @test table (
    Seg varchar(1),
    amt money
)

insert @test
select 'a', -427614103835526.00 union all
select 'a', -219289921481827.00 union all
select 'a', -169175063383313.00 union all
select 'a', -151094936902350.00 union all
select 'a', 522155575758567.00  union all
select 'a', 395662864132351.00  union all
select 'a', 187076841800608.00 

select SUM(amt) from @test

------ SCRIPT 2

 
declare
 @amt money

set @amt = 137721256088510.00

As you see, the script 1 will failed

Msg 8115, Level 16, State 2, Line 14
Arithmetic overflow error converting expression to data type money."

I tried to move the content to Excel and got the amount of the 2nd Script and it runs well. Is there any kind of explanation on this behavioral?

Thom A
  • 88,727
  • 11
  • 45
  • 75
Ivan Budiutama
  • 1
  • 3
    The final value isn't the only value that matters. As you aggregate you go over/under the min/max value and the error occurs. In truth, `money` is best avoided anyway. Switch to `decimal` with an appropriate scale and precision – Thom A May 06 '23 at 17:22
  • 1
    For example, let's say you have a `tinyint` and the values `254`, `2` and `-100`. The `SUM` of those values is `156` which fits in a `tinyint`. If, however, the data engine `SUM`s the values `254` and `2` first then an error occurs, as `256` is too large to fit in the data type. – Thom A May 06 '23 at 17:29

1 Answers1

2

To avoid overflows and to maintain a predictable ~15 significant digits in calculations, convert to float and then back.

select cast(SUM(cast(amt as float)) as money) from @test

This is not just for MONEY. Floating point math is more predictable and useful than the rules for decimal precision and scale in calculations with DECIMAL, which sacrifice precision to allow for potentially large results.

EG

declare @a decimal(38,8) = 2.0001
select @a*@a d, cast(@a as float)*cast(@a as float) f

outputs

d                                       f
--------------------------------------- ----------------------
4.000400                                4.00040001

(1 row affected)

Because the product of two decimal(38,8) values is decimal(38,6).

 select SQL_VARIANT_PROPERTY(@a*@a,'Precision') Precision, SQL_VARIANT_PROPERTY(@a*@a,'Scale') Scale

outputs

Precision             Scale
--------------------- -----------
38                    6
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • 2
    IMO - Casting to `decimal(19,4)` would be preferable. `SUM()` of that is `decimal(38,4)` and it avoids any imprecision from round tripping through `float`. Generally people value precision when dealing with financial calculations and the multiplication is not relevant – Martin Smith May 06 '23 at 19:14
  • agree with DECIMAL(19, 4) since it is commonly used. This [link](https://stackoverflow.com/questions/224462/storing-money-in-a-decimal-column-what-precision-and-scale) can give you more inshght – jmvcollaborator May 06 '23 at 20:01
  • In this case, decimal(19,4) is fine. In the case of more complex financial calculations loss of precision in a small intermediate result could be magnified in the final result. – David Browne - Microsoft May 06 '23 at 23:59