Money, Decimal or Numeric for Currency Columns

Question

I am creating a SQL table to hold transactions:

create table dbo.TRANSACTIONS
(
  Id int identity not null,
  Amount money not null
);

For currency (I am using euros) should I use money, decimal, or numeric?

I have seen the three being applied to currency columns so I am not sure anymore.

Money would be the obvious choice ... But I have seen decimal and numeric to.

By the way, I am using SQL Server 2012.

Thank You

score 7 · Accepted Answer · edited May 23 '17 at 11:57

7

First of all, Decimal and Numeric have the same functionality (MSDN info about it)

To answer the new question money VS decimal, there is already a Stackoverflow question about it: Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server? - the short answer was:

Never ever should you use money it is not precise and it is pure garbage, always use decimal/numeric

by SQLMenace

edited May 23 '17 at 11:57

Community

1
1

answered Feb 23 '14 at 18:00

peter

14,348
9
62
96

I have read that ... I was considering using decimal(20, 4) before I read that post where they use decimal(19,4). Why decimal(19,4) instead of decimal(20, 8) for example? Any reason for it? – Miguel Moura Feb 23 '14 at 18:15
1

you use 4 for scale (s) since most of the currencies are precise for 4 decimal comas. And you use 19 as the precision (p) because 19 is the highest precision for which decimal uses 9 storage bytes. When you use 20 for p you will use 13 bytes. You can read it in the [MSDN link in my answer](http://msdn.microsoft.com/en-us/library/ms187746.aspx) so if you want to have a higher precision go directly for 28 – peter Feb 23 '14 at 18:21
Any reason to use 9 storage bytes? In this case I will use it to store payments and do calculations on it ... I don't think I will need such a high precision. If I use a small one, like 12, will not the calculations be faster or do I need to go to 9 so I get 5 storage bytes? – Miguel Moura Feb 23 '14 at 19:04
2

In the table you can see that you will use 5 storage bytes only when your precision is 9 or less which gives you for a `s` of 4 only a maximum amount of 99,999. I wouldn't micro optimize here and just go for the most common values. – peter Feb 23 '14 at 19:06

score 2 · Answer 2 · edited May 23 '17 at 10:26

2

Decimal and Numeric are for almost all purposes, equivalent

Money is not a standard Sql type, and there may be other reasons to avoid it as well.

So choose an appropriate scale and precision and use DECIMAL.

Re : DECIMAL(19,4) vs (20,4)

The precision vs storage table here sums things up.

19,4 will store a value of 999 999 999 999 999.9999, at a cost of 9 bytes. A precision of 20 would require 13 bytes, which IMO would be a waste unless you needed the extra precision (in which case you can go to a precision of 28 with the same storage).

Also, for the same 9 bytes, you could also use e.g. 19,2, which will store 99 999 999 999 999 999.99

edited May 23 '17 at 10:26

Community

1
1

answered Feb 23 '14 at 18:02

StuartLC

104,537
17
209
285

I was going to use decimal(20,4) ... Do you think this is reasonable? – Miguel Moura Feb 23 '14 at 18:13
2

I've updated. I would go with 19,4 unless you really need the extra precision. FWIW 19,4 stores the US deficit of >USD 20 Trillion. Also, if you don't need 4 places after decimal, you can e.g. use 19,2 to allow larger values. – StuartLC Feb 23 '14 at 18:24
1

In this case I am storing only payments on a web site. So do I need such an high precision? I think the default for decimal is (18, 4). Is there a big difference between 18 and 19 in terms of performance? I believe in terms of space it is the same since the bytes are the same unless I use (9, 4) which is 5 storage bytes. – Miguel Moura Feb 23 '14 at 19:07
1

You know your requirements better than we do :) But, yes, you have the principal. `9,4` gives you a maximum value of `99 999.9999`. As per zahorek, unless there is a business requirement otherwise, precisions of 9, 19, 28 or 38 are generally the 'quanta'. – StuartLC Feb 23 '14 at 19:26

score 0 · Answer 3 · answered Feb 23 '14 at 18:01

0

just use integer if you have big data and want to efficently memorize data. When you view data in frontend just devide the number by 100.

answered Feb 23 '14 at 18:01

user3258791

1
1

score 0 · Answer 4 · answered Feb 23 '14 at 18:01

Sounds like you are working in a single currency (euros) so I would use decimal rather than money. The main advantage of money is that it can be displayed in a "locale friendly" way.

Money has limited precision, so it is more subject to rounding errors. Adds and subtracts are fine, but if you get into dividing money by money (for percentages or ratios), you would lose precision, especially over repeated operations.

Money, Decimal or Numeric for Currency Columns

4 Answers4