Is SQL Server 'MONEY' data type a decimal floating point or binary floating point?

Question

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) ?

Solution 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).

Solution 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.

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.

Solution 3

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

Comments

[Mark Byers]

Are you sure that DECIMAL needs one byte extra to store the scale? Isn't it stored as part of the column type?

[hobodave]

@Mark: Hmm, I see your point, but it's gotta be used to store something right? It's either the precision, or the scale.

[Mark Byers]

@hobodave: Well I thought I did... You can read my answer, but it has been downvoted, so maybe it's wrong? (It would have been nice if the downvoter said why!)

[hobodave]

You hijacked my answer and made it fancier. :-P

[kaptan]

yeah I missed the point that it is actually fixed point, and I think we can say that it is decimal fixed point.

[kaptan]

tnx for the write up but the whole point is that MONEY is fixed point which makes it a completely different beast.

[Mark Byers]

@Farzad: No, it's not a completely different beast. DECIMAL is fixed point too. The scaling is fixed in both types, but with the DECIMAL type you can choose what it is fixed to, but with MONEY you cannot.

[kaptan]

@Mark: no DECIMAL is decimal floating point. As u can read in the description it says: s (scale) The >>>>maximum<<<< number of decimal digits that can be stored to the right of the decimal point. This means that for DECIMAL (4,2) , 12.34 and 123.4 are valid numbers. The decimal point is floating as u can see.

[kaptan]

my bad. u r right. i had this wrong assumption that sql decimal is the same as .net decimal. it seems that as you said sql decimal is fixed-point too!