Is SQL Server 'MONEY' data type a decimal floating point or binary floating point?
I couldn't find anything that rejects or confirms whether SQL Server 'MONEY' data type is a decimal floating point or binary floating point.
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.
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) ?
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).
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.
Compare to decimal:
When maximum precision is used, valid values are from -10^38 + 1 through 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.
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.
I think the primary difference will be the storage space required.
DECIMAL(19,4) will require 9 storage bytes
MONEY will require 8 storage bytes