This sounds like a silly question, but I've noticed that in a lot of table designs for e-commerce related projects I almost always see decimal(19, 4) being used for currency.
这听起来像一个愚蠢的问题，但我注意到在电子商务相关项目的很多表设计中，我几乎总是看到十进制（19,4）用于货币。
Why the 4 on scale? Why not 2?
为什么4规模？为什么不是2？
Perhaps I'm missing a potential calculation issue down the road?
也许我错过了一个潜在的计算问题？
4 个解决方案
#1
12
First off - you are receiving some incorrect advice from other answers. Obseve the following (64-bit OS on 64-bit architecture):
首先 - 您从其他答案中收到一些不正确的建议。请注意以下内容（64位架构上的64位操作系统）：
declare @op1 decimal(18,2) = 0.01
,@op2 decimal(18,2) = 0.01;
select result = @op1 * @op2;
result
---------.---------.---------.---------
0.0001
(1 row(s) affected)
Note the number of underscores underneath the title - 39 in all. (I changed every tenth to a period to aid counting.) That is precisely enough for 38 digits (the maximum allowable, and the default on a 64 bit CPU) plus a decimal point on display. Although both operands were declared as decimal(18,2) the calculation was performed, and reported, in decimal(38,4) datatype. (I am running SQL 2012 on a 64 bit machine - some details may vary based on machine architecture and OS.)
请注意标题下方的下划线数量 - 总共39个。 （我每十分钟更换一次以帮助计数。）这恰好足以容纳38位数（最大允许值，64位CPU上的默认值）加上显示的小数点。虽然两个操作数都被声明为十进制（18,2），但计算以十进制（38,4）数据类型执行并报告。 （我在64位计算机上运行SQL 2012 - 某些细节可能因机器架构和操作系统而异。）
Therefore, it is clear that no precision is being lost. On the contrary, only overflow can occur, not precision loss. This is a direct consequence of all calculations on decimal operands being performed as integer arithmetic. You will occasionally see artifacts of this in intelli-sense when the type of intermediate fields of decimal type are reported as being int instead.
因此，很明显没有丢失精度。相反，只能发生溢出，而不是精度损失。这是对作为整数运算执行的十进制操作数的所有计算的直接结果。当十进制类型的中间字段的类型被报告为int时，您偶尔会看到智能的工件。
Consider the example above. The two operands are both of type decimal(18,2) and are stored as being integers of value 1, with a scale of 2. When multiplied the product is still 1, but the scale is evaluated by adding the scales, to create a result of integer value 1 and scale 4, which is a value of 0.0001 and of type decimal(18,4), stored as an integer with value 1 and scale 4.
考虑上面的例子。这两个操作数都是十进制类型（18,2），并且存储为值为1的整数，其比例为2.乘以时，乘积仍为1，但是通过添加比例来评估比例，以创建整数值1和小数位4的结果，其值为0.0001和十进制类型（18,4），存储为值为1且小数位数为4的整数。
Read that last paragraph again.
再读一遍最后一段。
Rinse and repeat once more.
再次冲洗并重复。
In practice, on a 64 bit machine and OS, this is actually stored and carried forward as being of type *decimal (38,4) because the calculations are being done on a CPU where the extra bits are free.
实际上，在64位机器和OS上，这实际上是以* decimal（38,4）类型存储和转发的，因为计算是在额外位空闲的CPU上完成的。
To return to your question - All major currencies of the world (that I am aware of) only require 2 decimal places, but there are a handful where 4 are required, and there are financial transactions such as currency transactions and bond sales where 4 decimal places are mandated by law. When devising the money datatype Microsoft appears to have opted for the maximum scale that might be required rather than the normal scale required. Given how few transactions, and corporations, actually require precision greater than 19 digits this seems eminently sensible.
回到你的问题 - 世界上所有主要货币（我所知道的）只需要2位小数，但有少数需要4位，并且有金融交易，如货币交易和债券销售，其中4位小数地方是法律规定的。在设计money数据类型时，Microsoft似乎选择了可能需要的最大比例，而不是所需的正常比例。鉴于很少有交易和公司实际上要求精确度超过19位，这似乎是非常明智的。
If you have:
如果你有：
- A high expectation of only dealing with major currencies (which at the current time only require 2 digits of scale); and
- 高度期望只处理主要货币（目前只需要2位数的货币）;和
- No expectation of dealing with transactions that are mandated by law to require 4 digits of scale
- 没有期望处理法律规定要求4位数字的交易
then you would be safe to use type decimal with scale 2 (such as decimal(19,2) or decimal(18,2) or decimal(38,2)) instead of money. This will ease some of your conversions and, given the assumptions above, have no cost. A typical case where these assumptions are met is in a GL or Subledger accounting system tracking transactions to the penny. However, a stock- or bond-trading system would not meet these assumptions because 4 digits of scale are mandated by law in those case.
那么你可以安全地使用带有比例2的类型十进制（例如十进制（19,2）或十进制（18,2）或十进制（38,2））而不是钱。这样可以简化您的部分转化，并且根据上述假设，您可以免费使用。满足这些假设的典型情况是在GL或Subledger会计系统中跟踪到便士的交易。但是，股票或债券交易系统不符合这些假设，因为在这种情况下法律强制要求4位数字。
A way to distinguish the two cases is whether transactions are reported in cents or percents, which only require 2 digits of scale, or in basis points which require 4 digits of scale.
区分这两种情况的一种方法是交易是以美分还是百分比报告，只需要2位数的比例，或者需要4位数的基点。
If you are at all unsure as to which case applies to your programming circumstance, consult your Controller or Director of Finance as to the legal and GAAP requirements for your application. (S)he will be able to give you definitive advice.
如果您完全不确定哪种情况适用于您的编程环境，请咨询您的财务主管或财务总监，了解您的申请的法律和GAAP要求。 （S）他将能够给你最终的建议。
#2
2
Things like gas prices would use the extra "scale" positions. You've seen gas at $1.959 per gallon, right?
天然气价格之类的东西会使用额外的“规模”头寸。你见过每加仑1.959美元的汽油，对吧？
#3
0
When use decimal it's up to you how you want to use according to your business requirements.
使用十进制时，根据您的业务需求，您可以根据自己的需要进行操作。
But when you will use Money data type in sql by default it stores with 4 decimal places.
但是当你在sql中默认使用Money数据类型时，它会存储4个小数位。
#4
0
In SQL the 19 is amount of integers, the 4 is amount of decimals.
在SQL中，19是整数，4是小数。
If you only have 2 decimals and you store maybe a result of some calculations, which results in more than 2 decimals, theres "no way" to store those additional decimals.
如果你只有2个小数，并且你可能存储了一些计算结果，这导致超过2个小数，那么“无法”存储那些额外的小数。
Some currencies operates with more than 2 decimals.
某些货币的小数超过2位。
Use the data type decimal, not money.
使用十进制数据类型，而不是钱。
#1
12
First off - you are receiving some incorrect advice from other answers. Obseve the following (64-bit OS on 64-bit architecture):
首先 - 您从其他答案中收到一些不正确的建议。请注意以下内容（64位架构上的64位操作系统）：
declare @op1 decimal(18,2) = 0.01
,@op2 decimal(18,2) = 0.01;
select result = @op1 * @op2;
result
---------.---------.---------.---------
0.0001
(1 row(s) affected)
Note the number of underscores underneath the title - 39 in all. (I changed every tenth to a period to aid counting.) That is precisely enough for 38 digits (the maximum allowable, and the default on a 64 bit CPU) plus a decimal point on display. Although both operands were declared as decimal(18,2) the calculation was performed, and reported, in decimal(38,4) datatype. (I am running SQL 2012 on a 64 bit machine - some details may vary based on machine architecture and OS.)
请注意标题下方的下划线数量 - 总共39个。 （我每十分钟更换一次以帮助计数。）这恰好足以容纳38位数（最大允许值，64位CPU上的默认值）加上显示的小数点。虽然两个操作数都被声明为十进制（18,2），但计算以十进制（38,4）数据类型执行并报告。 （我在64位计算机上运行SQL 2012 - 某些细节可能因机器架构和操作系统而异。）
Therefore, it is clear that no precision is being lost. On the contrary, only overflow can occur, not precision loss. This is a direct consequence of all calculations on decimal operands being performed as integer arithmetic. You will occasionally see artifacts of this in intelli-sense when the type of intermediate fields of decimal type are reported as being int instead.
因此，很明显没有丢失精度。相反，只能发生溢出，而不是精度损失。这是对作为整数运算执行的十进制操作数的所有计算的直接结果。当十进制类型的中间字段的类型被报告为int时，您偶尔会看到智能的工件。
Consider the example above. The two operands are both of type decimal(18,2) and are stored as being integers of value 1, with a scale of 2. When multiplied the product is still 1, but the scale is evaluated by adding the scales, to create a result of integer value 1 and scale 4, which is a value of 0.0001 and of type decimal(18,4), stored as an integer with value 1 and scale 4.
考虑上面的例子。这两个操作数都是十进制类型（18,2），并且存储为值为1的整数，其比例为2.乘以时，乘积仍为1，但是通过添加比例来评估比例，以创建整数值1和小数位4的结果，其值为0.0001和十进制类型（18,4），存储为值为1且小数位数为4的整数。
Read that last paragraph again.
再读一遍最后一段。
Rinse and repeat once more.
再次冲洗并重复。
In practice, on a 64 bit machine and OS, this is actually stored and carried forward as being of type *decimal (38,4) because the calculations are being done on a CPU where the extra bits are free.
实际上，在64位机器和OS上，这实际上是以* decimal（38,4）类型存储和转发的，因为计算是在额外位空闲的CPU上完成的。
To return to your question - All major currencies of the world (that I am aware of) only require 2 decimal places, but there are a handful where 4 are required, and there are financial transactions such as currency transactions and bond sales where 4 decimal places are mandated by law. When devising the money datatype Microsoft appears to have opted for the maximum scale that might be required rather than the normal scale required. Given how few transactions, and corporations, actually require precision greater than 19 digits this seems eminently sensible.
回到你的问题 - 世界上所有主要货币（我所知道的）只需要2位小数，但有少数需要4位，并且有金融交易，如货币交易和债券销售，其中4位小数地方是法律规定的。在设计money数据类型时，Microsoft似乎选择了可能需要的最大比例，而不是所需的正常比例。鉴于很少有交易和公司实际上要求精确度超过19位，这似乎是非常明智的。
If you have:
如果你有：
- A high expectation of only dealing with major currencies (which at the current time only require 2 digits of scale); and
- 高度期望只处理主要货币（目前只需要2位数的货币）;和
- No expectation of dealing with transactions that are mandated by law to require 4 digits of scale
- 没有期望处理法律规定要求4位数字的交易
then you would be safe to use type decimal with scale 2 (such as decimal(19,2) or decimal(18,2) or decimal(38,2)) instead of money. This will ease some of your conversions and, given the assumptions above, have no cost. A typical case where these assumptions are met is in a GL or Subledger accounting system tracking transactions to the penny. However, a stock- or bond-trading system would not meet these assumptions because 4 digits of scale are mandated by law in those case.
那么你可以安全地使用带有比例2的类型十进制（例如十进制（19,2）或十进制（18,2）或十进制（38,2））而不是钱。这样可以简化您的部分转化，并且根据上述假设，您可以免费使用。满足这些假设的典型情况是在GL或Subledger会计系统中跟踪到便士的交易。但是，股票或债券交易系统不符合这些假设，因为在这种情况下法律强制要求4位数字。
A way to distinguish the two cases is whether transactions are reported in cents or percents, which only require 2 digits of scale, or in basis points which require 4 digits of scale.
区分这两种情况的一种方法是交易是以美分还是百分比报告，只需要2位数的比例，或者需要4位数的基点。
If you are at all unsure as to which case applies to your programming circumstance, consult your Controller or Director of Finance as to the legal and GAAP requirements for your application. (S)he will be able to give you definitive advice.
如果您完全不确定哪种情况适用于您的编程环境，请咨询您的财务主管或财务总监，了解您的申请的法律和GAAP要求。 （S）他将能够给你最终的建议。
#2
2
Things like gas prices would use the extra "scale" positions. You've seen gas at $1.959 per gallon, right?
天然气价格之类的东西会使用额外的“规模”头寸。你见过每加仑1.959美元的汽油，对吧？
#3
0
When use decimal it's up to you how you want to use according to your business requirements.
使用十进制时，根据您的业务需求，您可以根据自己的需要进行操作。
But when you will use Money data type in sql by default it stores with 4 decimal places.
但是当你在sql中默认使用Money数据类型时，它会存储4个小数位。
#4
0
In SQL the 19 is amount of integers, the 4 is amount of decimals.
在SQL中，19是整数，4是小数。
If you only have 2 decimals and you store maybe a result of some calculations, which results in more than 2 decimals, theres "no way" to store those additional decimals.
如果你只有2个小数，并且你可能存储了一些计算结果，这导致超过2个小数，那么“无法”存储那些额外的小数。
Some currencies operates with more than 2 decimals.
某些货币的小数超过2位。
Use the data type decimal, not money.
使用十进制数据类型，而不是钱。