SQL Server’s currency data types have some interesting international features. And some of the intricacies of those features have some interesting international implications. I figured as long as we were here I could talk about some of them….
The money and smallmoney topic in MSDN gives the basics of the datatypes:
Monetary data values from -2^63 (-922,337,203,685,477.5808) through
2^63 – 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit. Storage size is 8 bytes.
Monetary data values from – 214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit. Storage size is 4 bytes.
Both numbers are pretty much scaled integer types rather than floating point values (the latter would freak out a lot of people when it comes to money, so it makes sense to build the types this way). Though of course if you need more than four decimal places it is recommended that use the Decimal data type (there are apparently currencies for whom it is recommended to store more than four decimal places to help with complex calculations).
You also cannot include currency grouping separators (commas for en-US) unless you pass the value as a string — in which case you will want to be sure that the currency grouping and decimal separators all match the language of the session you are in. I usually like to put in straight numbers and not worry about dependencies on the language settings, myself.
But the really interesting information is in the topic entitled Using Monetary Data. What this datatype allows is any currency symbol to be put in front of the number, even if it is not in a string (enclosed by single quotes) in a Transact-SQL clause. Basically, all of the following currency signs are supported:
As a bit of trivia, if you look at the Using Monetary Data topic, it has the same table as above, sorted in code point order, with one exception: the Euro (U+20ac) is placed just before U+20a1. The reason for this is that once upon a time (in the original Books Online topic that shipped with the initial release of SQL Server 2000), the documentation listed U+20a0 as the euro.
Now the code in SQL Server did not do this (since that was not really the euro), and if you tried to use U+20a0 (₠, a.k.a. EURO-CURRENCY SIGN) as a currency sign in a money or smallmoney column, it would not work.
When they finally fixed the documentation, it was I suppose easier to update the table by updating the two entries without moving stuff around in the table….
Interestingly. I just looked in SQL Server 2005 Books Online and this table has not been updated there, either to add new entries or to fix that one ordering issue. Oops. But that is kind of minor, no sense worrying about that….
Now for the real problems — you knew there would be real problems, didn’t you? 🙂
There are 22 characters in the Currency Symbols block (only 11 of which SQL Server recognizes in this case). Most importantly, there are 41 characters in the Sc (Symbol, Currency) general category (only 18 of which SQL Server recognizes in this case). For both of these you can look to the links to see the list of currency symbols….
I would be a lot happier if SQL Server were looking for a return of UnicodeCategory.CurrencySymbol from CharUnicodeInfo.GetUnicodeCategory or some other convenient way of getting the currency symbols and treating them that way, of course.
Or alternately, it would be cool if they removed some of the items that no longer really exist since they have converted to the euro now, and maybe added some more in.
However, I will now take a step back and not ask for those features just yet….
Note that you can just insert your currency values with any of these currency symbols in front of them. And the values will be inserted. As Is. Which may not be what you want if you deal with €100 vs. ¥100 vs. ₩100 for example (since €100 is about ¥13,541 or ₩126,178 by today’s fix!).
Now that currency symbol is not stored, either — the currency’s identity is eliminated after the insert. Fill in your own disaster sequence on this one — and make sure to be careful of what you insert in your application….
Now I have worked with the Cloanto Currency Server several times, and would highly recommend them to people who would want to deal with different types of currencies and do conversions. It is pretty cool having the results at your fingertips and available through both automation and .NET, too.
Anyway, the best practice for SQL Server is just keep the money and smallmoney columns with a single currency, or store the currency type in another field. It will keep you from doing something you did not intend to do with the database. Big mistakes (where big is defined as scope of effect; the actual mistake is usually a small design issue) in these sorts of columns are the surest way to find oneself looking for another job….