Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!
Anyone had an experience of handling multiple currencies? Current schema uses decimal (9, 2) so clearly won't handle currencies with 3 decimal places. Is there any advantage using money or smallmoney over say decimal (n, 3) ? Have Googled but results far from conclusive.
Yes, I can't seem to see any great benefit from using money. It seems to encompass the currency symbol as well as the amount though it doesn't seem to handle different separators. Just as well do as you suggest & get the greater precision through decimal. This way, when I display the value on the page, I guess I'll get the right currency symbol, precision & separator formatting the value using the relevant CultureInfo.
I'm not even talking SQL when I say that I have always found money data types more hassle than they are worth. Currency symbols/mnemonics can be a nightmare, especially where systems have different locale settings; you are better off controlling these yourself.
The only benefits I can see to money over other data types either custom or base types is 1) It is then very clear regardless of column name what the column was designed to hold and 2) Using financial functions that expect money as inputs, you might find that the type used even if identical in dimensions and content to money gets converted before running through the function leading to performance issues. This may also be the case joining that field to others although you're unlikely to join money columns that frequently.