您应该在 SQL Server 中选择 MONEY 还是 DECIMAL(x,y) 数据类型? [英] Should you choose the MONEY or DECIMAL(x,y) datatypes in SQL Server?

问题描述

我很好奇 money 数据类型和类似 decimal(19,4)(这是货币使用的在内部，我相信).

I'm curious as to whether or not there is a real difference between the money datatype and something like decimal(19,4) (which is what money uses internally, I believe).

我知道 money 特定于 SQL Server.我想知道是否有令人信服的理由选择其中之一；大多数 SQL Server 示例(例如 AdventureWorks 数据库)使用 money 而不是 decimal 来获取价格信息等内容.

I'm aware that money is specific to SQL Server. I want to know if there is a compelling reason to choose one over the other; most SQL Server samples (e.g. the AdventureWorks database) use money and not decimal for things like price information.

我应该继续使用money 数据类型，还是使用decimal 有好处?钱是要输入的字符较少，但这不是一个有效的原因:)

Should I just continue to use the money datatype, or is there a benefit to using decimal instead? Money is fewer characters to type, but that's not a valid reason :)

推荐答案

永远不要使用金钱.不精确，纯属垃圾；始终使用十进制/数字.

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

运行这个看看我的意思:

Run this to see what I mean:

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

Output: 2949.0000 2949.8525

有些人说你不能用钱来分钱:

To some of the people who said that you don't divide money by money:

这是我计算相关性的查询之一，将其更改为货币会产生错误的结果.

Here is one of my queries to calculate correlations, and changing that to money gives wrong results.

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

这篇关于您应该在 SQL Server 中选择 MONEY 还是 DECIMAL(x,y) 数据类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！