I am wondering what's the best type for a price field in SQL Server for a shop-like structure?
我想知道，在SQL Server中，对于类似商店的结构来说，什么是最好的价格字段类型?
Looking at this overview we have data types called money, smallmoney, then we have decimal/numeric and lastly float and real.
看看这个概述，我们有称为money的数据类型，smallmoney，然后是十进制/数字，最后是浮点和实数。
Name, memory/disk-usage and value ranges:
名称、内存/磁盘使用和值范围:
- Money: 8 bytes (values: -922,337,203,685,477.5808 to +922,337,203,685,477.5807)
- 货币:8字节(值:- 922337203,203,685,477.5808到+922,337,203,685,477.5807)
- Smallmoney: 4 bytes (values: -214,748.3648 to +214,748.3647)
- 小钱:4字节(值:- 214748.3648到+ 214748.3647)
- Decimal: 9 [default, min. 5] bytes (values: -10^38 +1 to 10^38 -1 )
- 小数:9(默认,分钟。5)字节(价值:-10 38 + 1到10 ^ ^ 38个1)
- Float: 8 bytes (values: -1.79E+308 to 1.79E+308 )
- 浮动:8字节(值:-1.79E+308到1.79E+308)
- Real: 4 bytes (values: -3.40E+38 to 3.40E+38 )
- 实数:4字节(值:-3.40E+38到3.40E+38)
Is it really wise to store price values in those types? What about eg. INT?
在这些类型中存储价格值真的明智吗?是什么。INT ?
- Int: 4 bytes (values: -2,147,483,648 to 2,147,483,647)
- Int: 4字节(值:-2,147,483,648到2,147,483,647)
Lets say a shop uses dollars, they have cents, but I don't see prices being $49.2142342 so the use of a lot of decimals showing cents seems waste of SQL bandwidth. Secondly, most shops wouldn't show any prices near 200.000.000 (not in normal web-shops at least, unless someone is trying to sell me a famous tower in Paris)
假设一个商店使用美元，他们有美分，但是我不认为价格是49.2142342美元，所以使用大量小数表示美分似乎浪费了SQL带宽。其次，大多数商店在200.000.000附近不会显示任何价格(至少在普通的网上商店是不会显示的，除非有人想把巴黎的一座著名的塔卖给我)
So why not go for an int?
那么为什么不使用int类型呢?
An int is fast, its only 4 bytes and you can easily make decimals, by saving values in cents instead of dollars and then divide when you present the values.
int是快速的，它只有4个字节，你可以很容易地做小数，通过保存分值而不是美元，然后在显示值时再进行除法。
The other approach would be to use smallmoney which is 4 bytes too, but this will require the math part of the CPU to do the calc, where as Int is integer power... on the downside you will need to divide every single outcome.
另一种方法是使用4字节的smallmoney，但是这需要CPU的数学部分来做calc，因为Int是整数幂……不利的一面是，你需要分割每一个结果。
Are there any "currency" related problems with regional settings when using smallmoney/money fields? what will these transfer too in C#/.NET ?
在使用小额货币/货币领域时，是否存在与地区设置有关的“货币”问题?c# /中这些也会传递什么?净吗?
Any pros/cons? Go for integer prices or smallmoney or some other?
优点/缺点吗?选择整数价格还是小额钱还是其他?
What does your experience tell?
你的经历告诉我们什么?
7 个解决方案
#1
20
If you're absolutely sure your numbers will always stay within the range of smallmoney, use that and you can save a few bytes. Otherwise, I would use money. But remember, storage is cheap these days. The extra 4 bytes over 100 million records is still less than half a GB. As @marc_s points out, however, using smallmoney if you can will reduce the memory footprint of SQL server.
如果你绝对确信你的数字将始终保持在小额钱的范围内，那么使用它，你可以节省一些字节。否则，我会用钱。但请记住，现在存储空间很便宜。超过1亿个记录的额外4字节仍然小于0.5 GB。然而，正如@marc_s指出的那样，如果可以的话，使用smallmoney将减少SQL server的内存占用。
Long story short, if you can get away with smallmoney, do. If you think you might go over the max, use money.
长话短说，如果你能省点钱就行了。如果你认为你可能会超过最大值，那就用钱吧。
But, do not use a floating-decimal type or you will get rounding issues and will start losing or gaining random cents, unless you deal with them properly.
但是，不要使用浮点小数类型，否则会出现四舍五入问题，并开始丢失或获得随机的分值，除非您正确地处理它们。
My argument against using int: Why reinvent the wheel by storing an int and then having to remember to divide by 100 (10000) to retrieve the value and multiply back when you go to store the value. My understanding is the money types use an int or long as the underlying storage type anyway.
我反对使用int:为什么要重新创建循环，存储一个int，然后必须记住除以100(10000)来检索值，并在存储值时进行乘法运算。我的理解是货币类型使用int或long作为底层存储类型。
As far as the corresponding data type in .NET, it will be decimal (which will also avoid rounding issues in your C# code).
至于。net中相应的数据类型，它将是十进制(这也将避免c#代码中的四舍五入问题)。
#2
9
Use the Money datatype if you are storing money (unless modelling huge amounts of money like the national debt) - it avoids precision/rounding issues.
如果你正在存钱，使用货币数据类型(除非建模大量的货币，如国债)——它避免了精确/四舍五入的问题。
The Many Benefits of Money…Data Type!
金钱的许多好处…数据类型!
#3
7
USE NUMERIC / DECIMAL. Avoid MONEY / SMALLMONEY. Here's an example of why. Sooner or later the MONEY / SMALLMONEY types will likely let you down due to rounding errors. The money types are completely redundant and achieve nothing useful - a currency amount being just another decimal number like any other.
使用数字/小数。避免资金/ SMALLMONEY。这里有一个例子说明为什么。由于四舍五入的错误，钱/小钱类型迟早会让你失望。货币类型是完全冗余的，没有任何用处——货币数量只是其他小数。
Lastly, the MONEY / SMALLMONEY types are proprietary to Microsoft. NUMERIC / DECIMAL are part of the SQL standard. They are used, recognised and understood by more people and are supported by most DBMSs and other software.
最后，金钱/小钱类型是微软的专利。数字/小数是SQL标准的一部分。它们被更多的人使用、识别和理解，并得到大多数dbms和其他软件的支持。
#4
4
Personally, I'd use smallmoney or money to store shop prices.
就我个人而言，我会用小钱或小钱来存储商店的价格。
Using int adds complexity elsewhere.
使用int在其他地方增加了复杂性。
And 200 million is perfectly valid price in Korean Won or Indonesian Rupees too...
2亿也是韩圆或印尼卢比的有效价格……
#5
3
SQL data types money and smallmoney both resolve to c# decimal type:
SQL数据类型money和smallmoney都解析为c#十进制类型:
http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqlmoney(v=VS.71).aspx
http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqlmoney(v = VS.71). aspx
So I'm thinking that you might as well go for decimal. Personally I've been using double all my life working in the financial industry and haven't experienced performance issues, etc. Actually, I've found that for certain calculations, etc., having a larger data type allows for higher degree of accuracy.
我想你们也可以用小数。就我个人而言，我一生都在从事金融行业，而且从未遇到过性能问题等等。事实上，我发现，对于某些计算，等等，拥有更大的数据类型可以获得更高的准确性。
#6
1
I would go for the Money datatype. Invididually you may not exceed the value in Smallmoney, but it would be easy for multiple items to exceed it.
我会选择货币数据类型。在短期内，你可能不会超过小额钱的价值，但是多个项目很容易超过它。
#7
0
In my pawnshop app, the pawnshop operators lend from $5.00 to $10,000.00 When they calculate the loan amount they round it to the nearest dollar in order to avoid dealing with cents (the same applies for interest payments). When the loan amount is above $50.00 they will round it to the nearest $5.00 (i.e. $50, $55, $60 ...), again to minimize running out of dollar bills. Therefore, I use DECIMAL(7,2) for transaction.calculated_loan_amount and DECIMAL(5,0) for transaction.loan_amount. The app calculates the loan amount to the penny and places that amount in loan_amount where it gets rounded to the nearest dollar when below $50 or to the nearest $5.00 when greater.
在我的典当行应用程序中，典当行运营商在计算出借金额时，会从5美元到1万美元不等(利息支付也是如此)。当贷款金额超过$50.00时，他们会将其四舍五入到最近的$5.00(即$50、$55、$60…)，以减少美元钞票的短缺。因此，我使用DECIMAL(7,2)来处理事务。calculated_loan_amount和DECIMAL(5,0)表示transaction.loan_amount。该应用程序计算贷款金额到便士，并将贷款金额以loan_amount表示，当贷款金额小于50美元时，它将四舍五入到最近的一美元，当贷款金额大于50美元时，它将四舍五入到最近的一美元。
#1
20
If you're absolutely sure your numbers will always stay within the range of smallmoney, use that and you can save a few bytes. Otherwise, I would use money. But remember, storage is cheap these days. The extra 4 bytes over 100 million records is still less than half a GB. As @marc_s points out, however, using smallmoney if you can will reduce the memory footprint of SQL server.
如果你绝对确信你的数字将始终保持在小额钱的范围内，那么使用它，你可以节省一些字节。否则，我会用钱。但请记住，现在存储空间很便宜。超过1亿个记录的额外4字节仍然小于0.5 GB。然而，正如@marc_s指出的那样，如果可以的话，使用smallmoney将减少SQL server的内存占用。
Long story short, if you can get away with smallmoney, do. If you think you might go over the max, use money.
长话短说，如果你能省点钱就行了。如果你认为你可能会超过最大值，那就用钱吧。
But, do not use a floating-decimal type or you will get rounding issues and will start losing or gaining random cents, unless you deal with them properly.
但是，不要使用浮点小数类型，否则会出现四舍五入问题，并开始丢失或获得随机的分值，除非您正确地处理它们。
My argument against using int: Why reinvent the wheel by storing an int and then having to remember to divide by 100 (10000) to retrieve the value and multiply back when you go to store the value. My understanding is the money types use an int or long as the underlying storage type anyway.
我反对使用int:为什么要重新创建循环，存储一个int，然后必须记住除以100(10000)来检索值，并在存储值时进行乘法运算。我的理解是货币类型使用int或long作为底层存储类型。
As far as the corresponding data type in .NET, it will be decimal (which will also avoid rounding issues in your C# code).
至于。net中相应的数据类型，它将是十进制(这也将避免c#代码中的四舍五入问题)。
#2
9
Use the Money datatype if you are storing money (unless modelling huge amounts of money like the national debt) - it avoids precision/rounding issues.
如果你正在存钱，使用货币数据类型(除非建模大量的货币，如国债)——它避免了精确/四舍五入的问题。
The Many Benefits of Money…Data Type!
金钱的许多好处…数据类型!
#3
7
USE NUMERIC / DECIMAL. Avoid MONEY / SMALLMONEY. Here's an example of why. Sooner or later the MONEY / SMALLMONEY types will likely let you down due to rounding errors. The money types are completely redundant and achieve nothing useful - a currency amount being just another decimal number like any other.
使用数字/小数。避免资金/ SMALLMONEY。这里有一个例子说明为什么。由于四舍五入的错误，钱/小钱类型迟早会让你失望。货币类型是完全冗余的，没有任何用处——货币数量只是其他小数。
Lastly, the MONEY / SMALLMONEY types are proprietary to Microsoft. NUMERIC / DECIMAL are part of the SQL standard. They are used, recognised and understood by more people and are supported by most DBMSs and other software.
最后，金钱/小钱类型是微软的专利。数字/小数是SQL标准的一部分。它们被更多的人使用、识别和理解，并得到大多数dbms和其他软件的支持。
#4
4
Personally, I'd use smallmoney or money to store shop prices.
就我个人而言，我会用小钱或小钱来存储商店的价格。
Using int adds complexity elsewhere.
使用int在其他地方增加了复杂性。
And 200 million is perfectly valid price in Korean Won or Indonesian Rupees too...
2亿也是韩圆或印尼卢比的有效价格……
#5
3
SQL data types money and smallmoney both resolve to c# decimal type:
SQL数据类型money和smallmoney都解析为c#十进制类型:
http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqlmoney(v=VS.71).aspx
http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqlmoney(v = VS.71). aspx
So I'm thinking that you might as well go for decimal. Personally I've been using double all my life working in the financial industry and haven't experienced performance issues, etc. Actually, I've found that for certain calculations, etc., having a larger data type allows for higher degree of accuracy.
我想你们也可以用小数。就我个人而言，我一生都在从事金融行业，而且从未遇到过性能问题等等。事实上，我发现，对于某些计算，等等，拥有更大的数据类型可以获得更高的准确性。
#6
1
I would go for the Money datatype. Invididually you may not exceed the value in Smallmoney, but it would be easy for multiple items to exceed it.
我会选择货币数据类型。在短期内，你可能不会超过小额钱的价值，但是多个项目很容易超过它。
#7
0
In my pawnshop app, the pawnshop operators lend from $5.00 to $10,000.00 When they calculate the loan amount they round it to the nearest dollar in order to avoid dealing with cents (the same applies for interest payments). When the loan amount is above $50.00 they will round it to the nearest $5.00 (i.e. $50, $55, $60 ...), again to minimize running out of dollar bills. Therefore, I use DECIMAL(7,2) for transaction.calculated_loan_amount and DECIMAL(5,0) for transaction.loan_amount. The app calculates the loan amount to the penny and places that amount in loan_amount where it gets rounded to the nearest dollar when below $50 or to the nearest $5.00 when greater.
在我的典当行应用程序中，典当行运营商在计算出借金额时，会从5美元到1万美元不等(利息支付也是如此)。当贷款金额超过$50.00时，他们会将其四舍五入到最近的$5.00(即$50、$55、$60…)，以减少美元钞票的短缺。因此，我使用DECIMAL(7,2)来处理事务。calculated_loan_amount和DECIMAL(5,0)表示transaction.loan_amount。该应用程序计算贷款金额到便士，并将贷款金额以loan_amount表示，当贷款金额小于50美元时，它将四舍五入到最近的一美元，当贷款金额大于50美元时，它将四舍五入到最近的一美元。