-2

What is the best datatype to store numeric values for billion dollars with 7 decimal values in SQL Server? Currently I am using NUMERIC(19, 7) however, I am not sure if this is correct because I don't have data to test it.

Aura
  • 1,283
  • 2
  • 16
  • 30
  • 1
    7 decimals?!? You need 2 decimals to store cents. – jarlh Jan 29 '19 at 15:24
  • 1
    Other than the fact that you don't need 7 decimal places for dollar amount, that would hold at least 12 digits to the left of the decimal point. – D Stanley Jan 29 '19 at 15:26
  • We need to store 7 decimals, that's why I have added 7 decimals. – Aura Jan 29 '19 at 15:26
  • 2
    If you need to store 7 decimals, your requirements are already more than just storing money, which makes this difficult for us to answer. – HoneyBadger Jan 29 '19 at 15:28
  • 1
    Use decimal and make sure to leave enough digits on the left part, not just for particular values but also consider month/year aggregates, etc. – EzLo Jan 29 '19 at 15:32
  • Use `DECIMAL(38, 10)`. It should be more than big enough to store all your values, and you don't have to think about the type. – Gordon Linoff Jan 29 '19 at 16:53

3 Answers3

3

I would use MONEY personally, which is specifically designed to hold monetary values:

-922,337,203,685,477.5808 to 922,337,203,685,477.5807

This will handle trillions so your billion-dollar values should be fine.

Reference article: https://learn.microsoft.com/en-us/sql/t-sql/data-types/money-and-smallmoney-transact-sql?view=sql-server-2017

Martin
  • 16,093
  • 1
  • 29
  • 48
  • This would be my first choice as well, but OP needs 7 decimals. – John Cappelletti Jan 29 '19 at 15:28
  • 2
    @JohnCappelletti I do see that now. The originaly question did not mention requiring 7 decimals, hence my (now redundant) answer – Martin Jan 29 '19 at 15:29
  • Thanks for your response @MartinParkin. My bad I did not realize its a specific requirement until mentioned by everyone. – Aura Jan 29 '19 at 15:30
  • 1
    Money is a pretty scary datatype for anything. It can have rounding errors. I would prefer to use decimal/numeric any day. https://stackoverflow.com/a/582819/3813116 – Sean Lange Jan 29 '19 at 15:31
  • Thanks @SeanLange, I think I would stick to `NUMERIC(19,7)` as it looks like it would be a safe bet in my case. – Aura Jan 29 '19 at 15:33
  • @SeanLange This is one time I disagree with you. Money is a perfectly valid type. After all, even decimal will round ... Select cast(25.255 as decimal(10,2)) = 25.26 – John Cappelletti Jan 29 '19 at 15:34
  • @JohnCappelletti I usually avoid the money datatype anyway because it is MS proprietary. I guess it is classified as an exact numeric so I stand corrected. :) However, I don't see the rounding error in your above example. It has to round it or truncate (or error) to fit in the smaller datatype. – Sean Lange Jan 29 '19 at 15:36
  • @SeanLange The proprietary is a valid point. My point is any precision would round ... 25.255 is not 25.26 It is NOT a rounding error, but it is rounded. – John Cappelletti Jan 29 '19 at 15:42
  • @JohnCappelletti agreed. Did you see the example in that link? It is off by 85¢ on a pretty simple calculation. – Sean Lange Jan 29 '19 at 15:43
  • @SeanLange Perfectly valid point. I may have to soften my position. :) – John Cappelletti Jan 29 '19 at 16:15
1

Want to cover everything? Use "DECIMAL(28,8)" it takes 4 extra bytes, but I think you shouldn't care about performance/space with those amounts and required precision.

Slava Murygin
  • 1,951
  • 1
  • 10
  • 10
0

I would use: for 1 to 9 billiondecimal(12,2), for 10 to 99 billiondecimal(13,2), for 100 to 999 billiondecimal(14,2).

Maciej Danielkiewicz
  • 1
  • 1