SQL Server的“MONEY”数据类型是十进制浮点数还是二进制浮点数?

I couldn't find anything that rejects or confirms whether SQL Server 'MONEY' data type is a decimal floating point or binary floating point.

我找不到任何拒绝或确认SQL Server 'MONEY'数据类型是十进制浮点数或二进制浮点数的东西。

In the description it says that MONEY type range is from -2^63 to 2^63 - 1 so this kind of implies that it should be a binary floating point.

描述说,钱的类型范围是2 ^ 63 - 2 ^ 63 - 1这种暗示,它应该是一个二进制浮点数。

But on this page it lists MONEY as "exact" numeric. Which kind of suggests that MONEY might be a decimal floating point (otherwise how is it exact? or what is the definition of exact?)

但在这个页面上，它列出的钱是“确切的”数字。哪一种观点认为货币可能是十进制浮点数(否则它如何准确?确切的定义是什么?)

Then if MONEY is a decimal floating point, then what is the difference between MONEY and DECIMAL(19,4) ?

那么，如果钱是一个小数浮点数，那么钱和小数的区别是什么(19,4)?

3 个解决方案

#1

Neither. If it were an implementation of floating point it would be subject to the same inaccuracies as FLOAT and REAL types. See Floating Point on wikipedia.

既不。如果它是浮点数的实现，它将会受到与浮动和实际类型相同的不准确。参见*上的浮点数。

MONEY is a fixed point type.

金钱是一种固定的类型。

It's one byte smaller than a DECIMAL(19,4), because it has a smaller range (922,337,203,685,477.5808 to 922,337,203,685,477.5807) as opposed to (-10^15+1 to 10^15-1).

这是一个字节小于小数(19日4),因为它有一个小范围(922337203685477 .5808 922337203685477 .5807)与(-10 ^ 15 + 1到10 ^ 15 - 1)。

#2

To see the differences we can look at the documentation:

要了解差异，我们可以查看文档:

Documentation for money:

文档:

Data type  Range                                                 Storage
money      -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes
smallmoney -214,748.3648 to 214,748.3647                         4 bytes

The money and smallmoney data types are accurate to a ten-thousandth of the monetary units that they represent.

货币和小钱数据类型的准确性是它们所代表的货币单位的1万倍。

Compare to decimal:

比较小数:

When maximum precision is used, valid values are from -10^38 + 1 through 10^38 - 1.

当使用最大精度,有效的值从-10年38 + 1到10 ^ ^ 38 - 1。

Precision    Storage
1 - 9        5 bytes
10 - 19      9 bytes
20 - 28      13 bytes
29 - 38      17 bytes

So they're not exactly equivalent, just similar. A DECIMAL(19,4) has a slightly greater range than MONEY (it can store from -10^15 + 0.0001 to 10^15 - 0.0001), but also needs one more byte of storage.

所以它们不是完全相等的，只是相似而已。小数(4)有一个稍微比金钱更大范围(它可以存储-10 ^ 15 + 0.0001到10 ^ 15 - 0.0001),但是也需要一个字节的存储。

In other words, this works:

换句话说，这行得通:

CREATE TABLE Table1 (test DECIMAL(19,4) NOT NULL);
INSERT INTO Table1 (test) VALUES
(999999999999999.9999);
SELECT * FROM Table1 

999999999999999.9999

But this doesn't:

但这并不是:

CREATE TABLE Table1 (test MONEY NOT NULL);
INSERT INTO Table1 (test) VALUES
(999999999999999.9999);
SELECT * FROM Table1 

Arithmetic overflow error converting numeric to data type money.

There's also a semantic difference. If you want to store monetary values, it makes sense to use the type money.

还有语义上的差异。如果您想存储货币价值，那么使用类型货币是有意义的。

#3

I think the primary difference will be the storage space required.

我认为主要的区别在于所需要的存储空间。

DECIMAL(19,4) will require 9 storage bytes

十进制(19,4)需要9个存储字节

MONEY will require 8 storage bytes

钱需要8个字节的存储空间

#1