## Wednesday, November 11, 2009

This might not be a big revelation to many of you, but do you see any glaring similarities in the following datatypes?:

/*
DataType Range (Smallest to Largest) #Bytes
--------------------------------------------------------------------------
integer -2,147,483,648 to 2,147,483,647 4
smallmoney -214,748.3648 to 214,748.3647 4
--------------------------------------------------------------------------
bigint -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 8
money -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8
--------------------------------------------------------------------------
*/
That’s right… the money datatypes are really just integers in disguise.

And, unfortunately, these money datatypes carry the baggage of integer division with them.

Consider the following:

declare @m money
set
@m=500
select DivBy1600=@m/1600
,DivBy16000=@m/16000
,DivBy160000=@m/160000
,DivBy1600000=@m/1600000
,DivBy16000000=@m/16000000
/*
DivBy1600 DivBy16000 DivBy160000 DivBy1600000 DivBy16000000
------------- ------------- ------------- ------------- -------------
0.3125 0.0312 0.0031 0.0003 0.00
*/
You can see that when 500 is divided by 16,000, the result is truncated (not rounded) to 4 decimals… the result is 0.0312 and not 0.0313.

And that truncation of 4 decimal places can produce some unexpected results:

declare @m money
set
@m=500
select By1600=@m/1600*1600
,By16000=@m/16000*16000
,By160000=@m/160000*160000
,By1600000=@m/1600000*1600000
,By16000000=@m/16000000*16000000
/*
By1600 By16000 By160000 By1600000 By16000000
------------ ------------ ------------ ------------ ------------
500.00 499.20 496.00 480.00 0.00
*/
One would expect to come up with an answer of 500 for each of those… But nooooo.

Notice that the same phenomenon does not happen when doing the same operations on a decimal (or numeric) datatype:

declare @d decimal(5,2)
set @d=500
select DivBy1600=@d/1600
,DivBy16000=@d/16000
,DivBy160000=@d/160000
,DivBy1600000=@d/1600000
,DivBy16000000=@d/16000000
/*
DivBy1600 DivBy16000 DivBy160000 DivBy1600000 DivBy16000000
------------- ------------- ------------- ------------- -------------
0.3125000 0.03125000 0.003125000 0.0003125000 0.00003125000
*/
select By1600=@d/1600*1600
,By16000=@d/16000*16000
,By160000=@d/160000*160000
,By1600000=@d/1600000*1600000
,By16000000=@d/16000000*16000000
/*
By1600 By16000 By160000 By1600000 By16000000
----------------- ---------------- --------------- --------------- ---------------
500.0000000 500.00000000 500.000000000 500.0000000000 500.00000000000
*/
Here’s another money tidbit that may cause confusion among new users of T-SQL…

According to Books Online, you represent a money constant as a string of numbers with an optional currency symbol as a prefix. So you can represent a money value of 100 as \$100 or £100 or ¥100 or €100, for example. But of course this doesn’t take into account any conversion ratios, as the following demonstrates:

select Revelation=
case
when \$100=£100 and \$100=¥100 and \$100=€100
then 'Really? Dollars and Pounds and Yen and Euros are all equal??'
else 'Of course they''re different... Who are you kidding?'
end
/*
Revelation
-------------------------------------------------------------
Really? Dollars and Pounds and Yen and Euros are all equal??
*/
There is one interesting thing (though laughably trivial) about using the money datatype. T-SQL will allow you to convert strings with commas into money, but it will not allow you to convert them into decimal or numeric:

declare @m money, @d decimal(10,2)
set @m='1,234,567.89' /* This works fine */
set @d='1,234,567.89' /* Msg 8114: Error converting data type varchar to numeric */
Whoop-de-doo.

In reality, the commas are simply removed before the conversion… the actual quantity or placement of the commas is not validated in any way. For example, the following executes without error:

declare @m money
set
@m=',1,,2,3,,,4,5,6,7,,,.8,9,,'
select @m /* Returns 1234567.89 */
I’ve personally never used the money datatype, and I certainly never will. As the biblical saying goes: “For the love of money is the root of all inaccurate calculations and currency confusion… oh yeah, and evil too.”

1. Good stuff. I LOL at the part with the conversion with commas to numeric.... too funny.

2. Awesome as always. Again, I'll never forget about money. Although I will miss converting things to money.

3. A data type is just a set of constraints on the datum. So, i wouldn't say money is INT in disguise, but that it "has similar constraints". Then it's easier to realize when there are diversions.

Thanx for the post. Rather enlightening.

Please send me a note at kent.waldrop(at)lpsvcs.com

Kent

5. "And that truncation of 4 decimal places can produce some unexpected results:"

Do you have an example where the result is unexpected? Monetary calculations have used fixed precision math for a millennium. It may offend your sensibilities as a programmer, but (\$500 / 16000000 ) * 16000000 = \$0 in the real world.

6. Anonymous:

In the real world, what is (\$500 / \$16000) * \$16000? I don't know any accountants who agree that the result of that should be \$499.20.

7. Very nice and stuffed article..

I read all the post in your blog and your doing the best thing..Thank you so much..

investments