货币，十进制或数字，用于货币列

I am creating a SQL table to hold transactions:

我正在创建一个SQL表来保存事务:

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.

顺便说一下，我正在使用SQL Server 2012。

Thank You

谢谢你！

4 个解决方案

#1

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

首先，Decimal和Numeric具有相同的功能(MSDN信息)

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

要回答新的问题money VS decimal，已经有了关于它的*的问题:您应该选择SQL Server中的money还是decimal (x,y)数据类型?-简短的回答是:

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

永远不要使用金钱它不精确而且是纯粹的垃圾，总是使用十进制/数字

by SQLMenace

由SQLMenace

#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.

金钱不是一种标准的Sql类型，可能还有其他原因可以避免它。

So choose an appropriate scale and precision and use DECIMAL.

因此，选择适当的比例和精度，并使用十进制。

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

Re: DECIMAL(19,4)和(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).

19,4将存储一个值为999 999 999 999 999.9999，花费9字节。20的精度需要13个字节，除非您需要额外的精度(在这种情况下，您可以在相同的存储空间中达到28的精度)，否则在IMO上这将是一种浪费。

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

同样，对于相同的9字节，您也可以使用例如，19,2，它将存储99999 999 999 999 999.99。

#3

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.

如果你有大数据，想要有效地记忆数据，就用整数吧。当你在frontend查看数据时，它会偏离数字100。

#4

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.

金钱的精确度是有限的，所以更多的是舍入误差。加法和减法都很好，但是如果你把钱用金钱(比例或比率)来划分的话，你就会失去精确性，尤其是重复的操作。

#1