Sql Money vs decimal data types

[wurzel]

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.

[DimPrawn]

Decimal is more flexible and can hold a greater range and precision.

Money is actually an integer that is fixed to 4 decimal places and so counts 0.0001, 0.0002, 0.0003 instead of 1,2,3.


I'd go with decimal or the new vardecimal type.

http://msdn.microsoft.com/en-us/libr...3(SQL.90).aspx

[Sysman]

With currencies you might want 5 decimal places anyway

E.g. xe.com

[wurzel]

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.

[scotspine]

decimal is the recommended type.

[Sysman]

Yes, I can't seem to see any great benefit from using money.

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.

[AtW]

If you are planning on storing GBP denominated currencies then I'd recommend you use 64-bit long (bigint) types.

HTH

[Durbs]

Use Decimal or Numeric

(they both do the same thing, just choose the name you like best)

[DimPrawn]

Use Decimal or Numeric

(they both do the same thing, just choose the name you like best)

The question was money type vs decimal.

Crikey.

[London75]

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.