Thanks to everyone for their input.
I'm happy to see that you're all recommending decimal which is what i meant by 'numeric'. I thought the two were interchangeable?
Vedran Kesegic (10/11/2012)
Look here about exact numerics and approximate numerics:http://msdn.microsoft.com/en-us/library/ms187752.aspx
You definitely do not want to use approximate numerics in a financial application.
For storage of values use the type that can store all the digits and decimals you need, without rounding them. It can be money, or decimal(25, 13) or int or whatever data fits in without change.
For internal calculations you should use most precise type available. When done, convert it to type you will store it.
I recommend you this for internal calculations:
1) Use DECIMAL(25, 13) for all operands. It is a "magic" type. It is out of the scope to here explain why, it require understanding of microsoft's rules of retaining precision/scale after certain math operations. I'll write blog about it someday.
2) If you have three or more operations, subresult of each operation should be cast-ed to DECIMAL(25, 13). Looks ugly, but guaranties precision.
3) Do operations that increase result first, and then the ones that decrease it. For example "X*Z/Y" is better than "X/Y*Z". You might expect the same result, but it is not the case if subresult trimms decimals because they are gone out of scale.
That will guarantee you retaining precision of 13 decimals throughout entire your calculation process.
Good to know, thanks. I'm aware of microsoft's rules but I still have to look them up when I need to remember them.
Based on your link and my own googling, I should be able to convert incoming floats (I have no control over the source systems, unfortunately) to decimals without any further loss of precision. Is that right? It's only converting decimals to floats that loses accuracy?
It seemed odd to me that one source uses floats for everything and the other even just uses it for quantities.