I've read I should use money, but in todays fast paced world maybe that's obsolete now.
我读过我应该用钱,但在今天快节奏的世界里,也许现在已经过时了。
What should I use?
我该怎么用?
5 个解决方案
#1
12
No, money should still work.
不,钱应该仍然有效。
#2
13
Papuccino,
I don't recommend the types money and smallmoney unless you are certain the only arithmetic you plan to do is addition and subtraction. If you might be dealing with exchange rates, percentages, and so on, you risk real problems with these types.
除非你确定你计划做的唯一算术是加法和减法,否则我不推荐使用money和smallmoney类型。如果您可能正在处理汇率,百分比等问题,那么您可能会面临这些类型的实际问题。
Here's just one small example to show you a difference between using money, decimal, and float when division is involved. It's possible to come up with examples where the difference is much more dramatic.
这里只是一个小例子,告诉你在涉及分割时使用money,decimal和float之间的区别。有可能提出一些示例,其中差异更为显着。
declare @m1 money, @m2 money, @m3 money
declare @d1 decimal(19,4), @d2 decimal(19,4), @d3 decimal(19,4)
declare @f1 float, @f2 float, @f3 float;
set @m1 = 1.00;
set @m2 = 345.00;
set @m3 = @m1/@m2;
set @d1 = 1.00;
set @d2 = 345.00;
set @d3 = @d1/@d2;
set @f1 = 1.00;
set @f2 = 345.00;
set @f3 = @f1/@f2;
select @m3, @d3, @f3;
Result: 0.0028 0.0029 0.00289855072463768
结果:0.0028 0.0029 0.00289855072463768
Depending on the industry, there may be guidelines or regulations to help you decide on the right data type. There is no one right answer.
根据行业的不同,可能会有指导方针或规定来帮助您确定正确的数据类型。没有一个正确的答案。
Added remarks:
You are correct that money/money should not be money, but SQL Server (inexplicably) produces exactly that result: type money from the quotient of two money values. This is bogus, but as you see from the example below, it is what you get, even though it makes no sense:
你是正确的,金钱/金钱不应该是金钱,但SQL Server(莫名其妙)产生了这样的结果:从两个货币价值的商中输入货币。这是假的,但正如你从下面的例子中看到的那样,这是你得到的,即使它没有意义:
declare @m1 money, @m2 money;
declare @d1 decimal(19,4), @d2 decimal(19,4);
set @m1 = 1.00;
set @m2 = 345.00;
set @d1 = 1.00;
set @d2 = 345.00;
select @m1/@m2, @d1/@d2
Result: 0.0028 0.0028985507246376811
结果:0.0028 0.0028985507246376811
The result with type money, 0.0028, is 3-4% less than the correct result.
类型货币0.0028的结果比正确结果少3-4%。
Of course, there are many situations where you need to divide currency values. The danger of using the money type is that the quotient is the wrong type (and an answer not close enough to the correct one). Examples of questions that require dividing currency:
当然,在很多情况下您需要划分货币值。使用货币类型的危险在于商是错误的类型(并且答案与正确的类型不够接近)。需要划分货币的问题示例:
Suppose you exchange 320 Yuan and the bank gives you 47.3 US dollars. What is the exchange rate you've been given?
假设您兑换320元,银行给您47.3美元。你给的汇率是多少?
Suppose you invest $23 and a year later it's worth $31. What is your percent rate of return?
假设您投资23美元,一年后它的价值为31美元。你的回报率是多少?
Both of these calculations require dividing currency values.
这两种计算都需要划分货币值。
#3
2
Why should money be obsolete? It goes up to over 900 trillions, hundreds of times the Federal Government's budget -- what kind of money amounts do you possibly need to store?-) (I guess that maybe Zimbabwe dollars might eventually have been a problem, but they kept resetting it by multiples of billions and trillions, and last April it was finally suspended; they use US dollars or other foreign currencies now for payments & accounting in Zimbabwe).
为什么钱应该过时?它超过900万亿,是联邦*预算的数百倍 - 您可能需要存储多少钱? - )(我想也许津巴布韦最终可能会出现问题,但他们一直在重置它以数十亿和数万亿的倍数计算,去年4月它终于暂停;他们现在使用美元或其他外币进行津巴布韦的支付和会计处理。
#4
1
I agree money divided by money is bogus. But money divided by days is real. If you are dividing small amounts of money by the number of days you need to spead the cost, it is most important to keep watch of this phenomenon. I cast/convert money to float, do computation before eventually storing the end result to money data type. Hope this helps.
我同意用金钱划分的钱是假的。但钱除以天是真实的。如果您将少量资金划分为需要花费成本的天数,那么最重要的是要注意这种现象。我投资/转换资金到浮动,在最终将最终结果存储到货币数据类型之前进行计算。希望这可以帮助。
#5
0
It surprises me that nobody mentioned it before.
让我感到惊讶的是,之前没有人提到它。
money is 8 bytes. decimal is 5, 9, 13 or 17 bytes depending on precision (precision is not the number of decimal digits after the decimal point, it is the maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point). So, to mimic range of values that money supports (-922,337,203,685,477.5808 to 922,337,203,685,477.5807) you need decimal(19,4).
钱是8个字节。十进制是5,9,13或17个字节,具体取决于精度(精度不是小数点后的小数位数,它是将存储的最大小数位数,包括在左侧和右侧)小数点)。因此,为了模仿货币支持的价值范围(-922,337,203,685,477.5808至922,337,203,685,477.5807),您需要小数(19,4)。
+-------------------+---------------+
| decimal precision | Storage bytes |
+-------------------+---------------+
| 1 - 9 | 5 |
| 10-19 | 9 |
| 20-28 | 13 |
| 29-38 | 17 |
+-------------------+---------------+
If storing 9 bytes instead of 8 doesn't seem like a big deal, you should keep in mind that money is a native processor type, like 64-bit bigint, but decimal is not. It means that summing up billions of money values would be faster than summing up decimal values. Performing other calculations like division would have even larger difference.
如果存储9个字节而不是8个字节似乎不是什么大问题,那么你应该记住,钱是本机处理器类型,如64位bigint,但小数不是。这意味着总结数十亿货币价值将比累计小数值更快。执行其他计算(例如除法)会产生更大的差异。
On my virtual machine with SQL Server 2014 Express I ran this simple test. dbo.Numbers is a table with 10,000 rows with one int column Number with values from 1 to 10,000.
在我的SQL Server 2014 Express虚拟机上,我运行了这个简单的测试。 dbo.Numbers是一个包含10,000行的表,其中一列为Number,其值为1到10,000。
CREATE TABLE [dbo].[Numbers](
[Number] [int] NOT NULL,
CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED
(
[Number] ASC
))
I ran this in SQL Sentry Plan Explorer:
我在SQL Sentry Plan Explorer中运行了这个:
DECLARE @VarM money = 1234.5678;
SELECT
AVG(@VarM / N1.Number)
FROM
dbo.Numbers AS N1
CROSS JOIN dbo.Numbers AS N2
;
DECLARE @VarD decimal(19,4) = 1234.5678;
SELECT
AVG(@VarD / N1.Number)
FROM
dbo.Numbers AS N1
CROSS JOIN dbo.Numbers AS N2
;
Execution plan is the same for both queries (well, there are different implicit conversions of Number: to money and decimal), but run time is 15 seconds vs. 40 seconds. It is quite noticeable for me.
两个查询的执行计划是相同的(好吧,有不同的隐式转换数:金钱和小数),但运行时间是15秒而不是40秒。这对我来说非常明显。
Of course, you need to know that money has only 4 decimal places. If you perform calculations you need to be aware of the types (and their precedence, i.e. what is implicitly converted into what) and make sure that intermediate results are of the appropriate type by casting operands into proper types if needed. This warning applies to calculations with any types, not just money. When you divide two int values you should know that result is int and not be surprised that 4 / 5 = 0.
当然,你需要知道钱只有小数点后4位。如果执行计算,则需要了解类型(及其优先级,即隐式转换为什么),并通过在需要时将操作数转换为适当的类型来确保中间结果具有适当的类型。此警告适用于任何类型的计算,而不仅仅是金钱。当你划分两个int值时,你应该知道结果是int,并且不要惊讶于4/5 = 0。
#1
12
No, money should still work.
不,钱应该仍然有效。
#2
13
Papuccino,
I don't recommend the types money and smallmoney unless you are certain the only arithmetic you plan to do is addition and subtraction. If you might be dealing with exchange rates, percentages, and so on, you risk real problems with these types.
除非你确定你计划做的唯一算术是加法和减法,否则我不推荐使用money和smallmoney类型。如果您可能正在处理汇率,百分比等问题,那么您可能会面临这些类型的实际问题。
Here's just one small example to show you a difference between using money, decimal, and float when division is involved. It's possible to come up with examples where the difference is much more dramatic.
这里只是一个小例子,告诉你在涉及分割时使用money,decimal和float之间的区别。有可能提出一些示例,其中差异更为显着。
declare @m1 money, @m2 money, @m3 money
declare @d1 decimal(19,4), @d2 decimal(19,4), @d3 decimal(19,4)
declare @f1 float, @f2 float, @f3 float;
set @m1 = 1.00;
set @m2 = 345.00;
set @m3 = @m1/@m2;
set @d1 = 1.00;
set @d2 = 345.00;
set @d3 = @d1/@d2;
set @f1 = 1.00;
set @f2 = 345.00;
set @f3 = @f1/@f2;
select @m3, @d3, @f3;
Result: 0.0028 0.0029 0.00289855072463768
结果:0.0028 0.0029 0.00289855072463768
Depending on the industry, there may be guidelines or regulations to help you decide on the right data type. There is no one right answer.
根据行业的不同,可能会有指导方针或规定来帮助您确定正确的数据类型。没有一个正确的答案。
Added remarks:
You are correct that money/money should not be money, but SQL Server (inexplicably) produces exactly that result: type money from the quotient of two money values. This is bogus, but as you see from the example below, it is what you get, even though it makes no sense:
你是正确的,金钱/金钱不应该是金钱,但SQL Server(莫名其妙)产生了这样的结果:从两个货币价值的商中输入货币。这是假的,但正如你从下面的例子中看到的那样,这是你得到的,即使它没有意义:
declare @m1 money, @m2 money;
declare @d1 decimal(19,4), @d2 decimal(19,4);
set @m1 = 1.00;
set @m2 = 345.00;
set @d1 = 1.00;
set @d2 = 345.00;
select @m1/@m2, @d1/@d2
Result: 0.0028 0.0028985507246376811
结果:0.0028 0.0028985507246376811
The result with type money, 0.0028, is 3-4% less than the correct result.
类型货币0.0028的结果比正确结果少3-4%。
Of course, there are many situations where you need to divide currency values. The danger of using the money type is that the quotient is the wrong type (and an answer not close enough to the correct one). Examples of questions that require dividing currency:
当然,在很多情况下您需要划分货币值。使用货币类型的危险在于商是错误的类型(并且答案与正确的类型不够接近)。需要划分货币的问题示例:
Suppose you exchange 320 Yuan and the bank gives you 47.3 US dollars. What is the exchange rate you've been given?
假设您兑换320元,银行给您47.3美元。你给的汇率是多少?
Suppose you invest $23 and a year later it's worth $31. What is your percent rate of return?
假设您投资23美元,一年后它的价值为31美元。你的回报率是多少?
Both of these calculations require dividing currency values.
这两种计算都需要划分货币值。
#3
2
Why should money be obsolete? It goes up to over 900 trillions, hundreds of times the Federal Government's budget -- what kind of money amounts do you possibly need to store?-) (I guess that maybe Zimbabwe dollars might eventually have been a problem, but they kept resetting it by multiples of billions and trillions, and last April it was finally suspended; they use US dollars or other foreign currencies now for payments & accounting in Zimbabwe).
为什么钱应该过时?它超过900万亿,是联邦*预算的数百倍 - 您可能需要存储多少钱? - )(我想也许津巴布韦最终可能会出现问题,但他们一直在重置它以数十亿和数万亿的倍数计算,去年4月它终于暂停;他们现在使用美元或其他外币进行津巴布韦的支付和会计处理。
#4
1
I agree money divided by money is bogus. But money divided by days is real. If you are dividing small amounts of money by the number of days you need to spead the cost, it is most important to keep watch of this phenomenon. I cast/convert money to float, do computation before eventually storing the end result to money data type. Hope this helps.
我同意用金钱划分的钱是假的。但钱除以天是真实的。如果您将少量资金划分为需要花费成本的天数,那么最重要的是要注意这种现象。我投资/转换资金到浮动,在最终将最终结果存储到货币数据类型之前进行计算。希望这可以帮助。
#5
0
It surprises me that nobody mentioned it before.
让我感到惊讶的是,之前没有人提到它。
money is 8 bytes. decimal is 5, 9, 13 or 17 bytes depending on precision (precision is not the number of decimal digits after the decimal point, it is the maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point). So, to mimic range of values that money supports (-922,337,203,685,477.5808 to 922,337,203,685,477.5807) you need decimal(19,4).
钱是8个字节。十进制是5,9,13或17个字节,具体取决于精度(精度不是小数点后的小数位数,它是将存储的最大小数位数,包括在左侧和右侧)小数点)。因此,为了模仿货币支持的价值范围(-922,337,203,685,477.5808至922,337,203,685,477.5807),您需要小数(19,4)。
+-------------------+---------------+
| decimal precision | Storage bytes |
+-------------------+---------------+
| 1 - 9 | 5 |
| 10-19 | 9 |
| 20-28 | 13 |
| 29-38 | 17 |
+-------------------+---------------+
If storing 9 bytes instead of 8 doesn't seem like a big deal, you should keep in mind that money is a native processor type, like 64-bit bigint, but decimal is not. It means that summing up billions of money values would be faster than summing up decimal values. Performing other calculations like division would have even larger difference.
如果存储9个字节而不是8个字节似乎不是什么大问题,那么你应该记住,钱是本机处理器类型,如64位bigint,但小数不是。这意味着总结数十亿货币价值将比累计小数值更快。执行其他计算(例如除法)会产生更大的差异。
On my virtual machine with SQL Server 2014 Express I ran this simple test. dbo.Numbers is a table with 10,000 rows with one int column Number with values from 1 to 10,000.
在我的SQL Server 2014 Express虚拟机上,我运行了这个简单的测试。 dbo.Numbers是一个包含10,000行的表,其中一列为Number,其值为1到10,000。
CREATE TABLE [dbo].[Numbers](
[Number] [int] NOT NULL,
CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED
(
[Number] ASC
))
I ran this in SQL Sentry Plan Explorer:
我在SQL Sentry Plan Explorer中运行了这个:
DECLARE @VarM money = 1234.5678;
SELECT
AVG(@VarM / N1.Number)
FROM
dbo.Numbers AS N1
CROSS JOIN dbo.Numbers AS N2
;
DECLARE @VarD decimal(19,4) = 1234.5678;
SELECT
AVG(@VarD / N1.Number)
FROM
dbo.Numbers AS N1
CROSS JOIN dbo.Numbers AS N2
;
Execution plan is the same for both queries (well, there are different implicit conversions of Number: to money and decimal), but run time is 15 seconds vs. 40 seconds. It is quite noticeable for me.
两个查询的执行计划是相同的(好吧,有不同的隐式转换数:金钱和小数),但运行时间是15秒而不是40秒。这对我来说非常明显。
Of course, you need to know that money has only 4 decimal places. If you perform calculations you need to be aware of the types (and their precedence, i.e. what is implicitly converted into what) and make sure that intermediate results are of the appropriate type by casting operands into proper types if needed. This warning applies to calculations with any types, not just money. When you divide two int values you should know that result is int and not be surprised that 4 / 5 = 0.
当然,你需要知道钱只有小数点后4位。如果执行计算,则需要了解类型(及其优先级,即隐式转换为什么),并通过在需要时将操作数转换为适当的类型来确保中间结果具有适当的类型。此警告适用于任何类型的计算,而不仅仅是金钱。当你划分两个int值时,你应该知道结果是int,并且不要惊讶于4/5 = 0。