我很好奇货币数据类型和十进制(19,4)之类的数据类型之间是否有真正的区别(我相信这是货币内部使用的)。
我知道钱是特定于SQL Server的。我想知道是否有令人信服的理由来选择一个而不是另一个;大多数SQL Server样本(例如AdventureWorks数据库)使用金钱而不是小数来表示价格信息。
我是否应该继续使用货币数据类型,或者使用十进制是否有好处?金钱可以输入的字符更少,但这不是一个有效的理由:)
我很好奇货币数据类型和十进制(19,4)之类的数据类型之间是否有真正的区别(我相信这是货币内部使用的)。
我知道钱是特定于SQL Server的。我想知道是否有令人信服的理由来选择一个而不是另一个;大多数SQL Server样本(例如AdventureWorks数据库)使用金钱而不是小数来表示价格信息。
我是否应该继续使用货币数据类型,或者使用十进制是否有好处?金钱可以输入的字符更少,但这不是一个有效的理由:)
当前回答
作为对其他答案主旨的反驳。看金钱的诸多好处…数据类型!在SQLCAT的关系引擎指南中
我要具体指出以下几点
Working on customer implementations, we found some interesting performance numbers concerning the money data type. For example, when Analysis Services was set to the currency data type (from double) to match the SQL Server money data type, there was a 13% improvement in processing speed (rows/sec). To get faster performance within SQL Server Integration Services (SSIS) to load 1.18 TB in under thirty minutes, as noted in SSIS 2008 - world record ETL performance, it was observed that changing the four decimal(9,2) columns with a size of 5 bytes in the TPC-H LINEITEM table to money (8 bytes) improved bulk inserting speed by 20% ... The reason for the performance improvement is because of SQL Server’s Tabular Data Stream (TDS) protocol, which has the key design principle to transfer data in compact binary form and as close as possible to the internal storage format of SQL Server. Empirically, this was observed during the SSIS 2008 - world record ETL performance test using Kernrate; the protocol dropped significantly when the data type was switched to money from decimal. This makes the transfer of data as efficient as possible. A complex data type needs additional parsing and CPU cycles to handle than a fixed-width type.
所以这个问题的答案是“视情况而定”。对于某些算术操作,您需要更加小心,以保持精度,但您可能会发现,出于性能考虑,这样做是值得的。
其他回答
SQLMenace说钱是不精确的。但是你不能把钱乘以/除以钱!3美元乘以50美分等于多少?150 dollarcents ?你用标量乘(除)钱,结果应该是小数。
DECLARE
@mon1 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4)
SELECT
@mon1 = 100,
@num1 = 100, @num2 = 339, @num3 = 10000
SET @mon4 = @mon1/@num2*@num3
SET @num4 = @num1/@num2*@num3
SELECT @mon4 AS moneyresult,
@num4 AS numericresult
结果在正确的结果:
moneyresult numericresult --------------------- --------------------------------------- 2949.8525 2949.8525
钱是好的,只要你不需要超过4个十进制数字,你要确保你的标量(不代表钱)是小数。
我刚看到这个博客:SQL Server中的金钱vs.十进制。
这基本上是说金钱有一个精确的问题……
declare @m money
declare @d decimal(9,2)
set @m = 19.34
set @d = 19.34
select (@m/1000)*1000
select (@d/1000)*1000
对于货币类型,你将得到19.30而不是19.34。我不确定是否存在将钱分成1000份进行计算的应用场景,但这个示例确实暴露了一些局限性。
永远不要使用金钱。它不精确,纯粹是垃圾;总是使用十进制/数字。
运行这个来看看我的意思:
DECLARE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 DECIMAL(19,4),
@num2 DECIMAL(19,4),
@num3 DECIMAL(19,4),
@num4 DECIMAL(19,4)
SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000
SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3
SELECT @mon4 AS moneyresult,
@num4 AS numericresult
输出:2949.0000 2949.8525
有些人说钱不能一分一分
这是我计算相关性的一个问题,把它换成金钱会得到错误的结果。
select t1.index_id,t2.index_id,(avg(t1.monret*t2.monret)
-(avg(t1.monret) * avg(t2.monret)))
/((sqrt(avg(square(t1.monret)) - square(avg(t1.monret))))
*(sqrt(avg(square(t2.monret)) - square(avg(t2.monret))))),
current_timestamp,@MaxDate
from Table1 t1 join Table1 t2 on t1.Date = traDate
group by t1.index_id,t2.index_id
作为对其他答案主旨的反驳。看金钱的诸多好处…数据类型!在SQLCAT的关系引擎指南中
我要具体指出以下几点
Working on customer implementations, we found some interesting performance numbers concerning the money data type. For example, when Analysis Services was set to the currency data type (from double) to match the SQL Server money data type, there was a 13% improvement in processing speed (rows/sec). To get faster performance within SQL Server Integration Services (SSIS) to load 1.18 TB in under thirty minutes, as noted in SSIS 2008 - world record ETL performance, it was observed that changing the four decimal(9,2) columns with a size of 5 bytes in the TPC-H LINEITEM table to money (8 bytes) improved bulk inserting speed by 20% ... The reason for the performance improvement is because of SQL Server’s Tabular Data Stream (TDS) protocol, which has the key design principle to transfer data in compact binary form and as close as possible to the internal storage format of SQL Server. Empirically, this was observed during the SSIS 2008 - world record ETL performance test using Kernrate; the protocol dropped significantly when the data type was switched to money from decimal. This makes the transfer of data as efficient as possible. A complex data type needs additional parsing and CPU cycles to handle than a fixed-width type.
所以这个问题的答案是“视情况而定”。对于某些算术操作,您需要更加小心,以保持精度,但您可能会发现,出于性能考虑,这样做是值得的。
我意识到WayneM已经声明他知道钱是特定的SQL Server。然而,他问是否有任何理由使用货币而不是十进制,反之亦然,我认为一个明显的原因仍然应该说明,那就是使用十进制意味着如果你不得不改变你的DBMS,它可以减少一件担心的事情——这是可能发生的。
使您的系统尽可能灵活!