I am utilizing a decimal column to keep money values on the database, now I'm wondering what precision and scale to make use of.
Since allegedly char posts of the fixed width tend to be more efficient, I believed exactly the same might be true for decimal posts. Could it be?
And what precision and scale must i use? I believed precision 24/8. Is the fact that overkill, insufficient or ok?
This is exactly what I have made the decision to complete:
* Keep conversions (when relevant) within the transaction table itself, like a float * The currency is saved around the account table * The transaction amount is a DECIMAL(19,4) * All information utilizing a conversion rate is going to be handled on my small application and so i keep charge of rounding issues
I do not think a float for that conversion minute rates are an problem, becasue it is mostly for reference, and I'm going to be casting it to some decimal anyway.
Thanks all for the valuable input.
If you're searching for a 1-size-fits-all, I'd suggest
DECIMAL(19, 4) is really a popular choice (a fast Google bears this out). I believe this arises from that old VBA/Jet Currency data type, being the very first fixed point decimal enter in the language
Decimal only arrived 'version 1.0' style (i.e. not fully implemented) in VB6/VBA6/Jet 4..
The general rule for storage of fixed point decimal values would be to store a minumum of one more decimal place than you really require to permit rounding. A primary reason for implementing mapping that old Currency within the front-end to
DECIMAL(19, 4) within the back finish could be that the Currency type showed Microsoft's banker's rounding formula by character, whereas
DECIMAL(p, s) rounded by truncation.
An additional decimal devote storage for
DECIMAL enables a custom rounding formula to become implemented instead of taking the vendor's default (and banker's rounding is alarming, as you would expect, for any designer expecting all values ending ins5 to round from zero).
DECIMAL(24, 8) seems like overkill in my experience. Most foreign currencies are cited to 4 or 5 decimal places. I understand of situations in which a decimal scale of 8 (or even more) is needed but this is when a 'normal' financial amount (say four decimal places) continues to be professional rata'd, alluding to decimal precision ought to be reduced accordingly (also think about a floating point key in such conditions). With no you have much money nowadays to need a decimal precision of 24 :)
However, as opposed to a one-size-fits-all approach, some investigation might be so as. Request your designer or domain expert about accounting rules which might be relevant: GAAP, EU, etc. I vaguely recall some EU intra-condition transfers with explicit rules for rounding to 5 decimal places, therefore using
DECIMAL(p, 6) for storage. An accounting firm generally appear to favour four decimal places.
PS Avoid SQL Server's
MONEY data type since it has serious difficulties with precision when rounding, among other factors for example portability etc. See Aaron Bertrand's blog.