A quick look at this byte-reversed value 0x02B0A87C in a hex calculator gives us the amount of 45,, After moving the decimal point four places to the left, we get our starting value of This is even more noticeable when looking at negative values. Photo by Ibrahim Rifath on Unsplash.
Money is 8-bytes, smallmoney is 4-bytes, decimals with a precision of is 5 bytes and decimals with a precision of are 9 bytes.
This means that, in those columns with the datatype decimal ,4 , one can save either 1 or 5 bytes depending on the data within and the business requirements. The scientific world can tolerate tiny rounding errors and margins of error, but in finance a monetary calculation is either right or wrong.
Take the calculation in Listing 1, which is the simplest I can think of that illustrates the problem. Notice the lack of any currency symbols for the Portion and Total values.
Aside from that, though, are the percentage values correct? The following figure shows the results of doing the reverse calculations i. The fact that we cannot calculate the portion or total exactly, from the percentage values produced using the MONEY datatype Listing 1 , confirms that there are rounding errors in those percentage values.
You can also get scale overflow errors if you try to calculate correlations the classical way , when using MONEY values. The number of bytes required for storage is a consideration for all datatypes. However, with numeric datatypes a second potential issue must be considered: the precision of numerical calculations. Roundoff error will never appear in calculations involving only addition and subtraction, which constitute the majority of calculations performed on MONEY. While MONEY is safe for addition and subtraction, the minute division or multiplication is introduced, all bets are off.
This arises from the fact that SQL Server does arithmetic differently depending on the datatypes involved in the calculation. For example —. Since MONEY only represents four places to the right of the decimal, any calculation requiring 5 or more places in the fractional representation will incur roundoff error.
This is a perfectly reasonable scenario, suggesting that due to weird edge cases like this, money is not a good data type to use, as the result is always truncated back to fit money instead of following the normal rules for precision and scale. What if you want to calculate the standard deviation of a set of money values? Show 19 more comments. Community Bot 1 1 1 silver badge.
Anon Anon 9, 1 1 gold badge 26 26 silver badges 31 31 bronze badges. Can you explain what happened in this example? Scale overflow. Anon's example is version and database specific. I know we are talking about ms sql here.
I think this is the most enlightening answer because you've explained not simply just the errors that get propagated, but what's really going on behind the scene, and why MONEY exists in the first place.
I'm not sure why it took 4 years to get this answer, but perhaps it's because there's too much focus on the calculation "problem", and not so much on the whys and hows of money vs decimal.
Show 1 more comment. Make your systems as flexible as possible! Dean Dean 4, 5 5 gold badges 32 32 silver badges 44 44 bronze badges. Possibly, but in theory you could declare a domain named Money in another DBMS that supports the declaration of domains.
Try to avoid data types that are bespoke to a particular database platform unless there are very sound business reasons to use them. You should say "Don't use deprecated types when the database already provides better, more standard-compliant types and warns against the deprecated ones".
PanagiotisKanavos - money isn't deprecated — Martin Smith. MartinSmith shocked to see this, as it can't actually handle money values like BTC in Anyway, moving to Redshift introduces a lot of pain — Panagiotis Kanavos. Keith Pinson 7, 6 6 gold badges 55 55 silver badges 98 98 bronze badges. You can save size, worry less about rounding errors, and make your code more portable. While JonofAllTrades is correct, you could also get integer performance back by simply using an integer containing pennies or cents, and save the extra byte that encodes the position of the decimal point, and which has to be checked when adding decimals together.
You can never predict how people will end up querying the system you are creating, best to avoid the possibility of misuse when you can. The minuscule gain in storage is not worth it IMHO. Try storing a Bitcoin value.
It has 8 decimals — Panagiotis Kanavos. Kaideejee Kaideejee 1 1 silver badge 2 2 bronze badges. But it only does so because an ignorant developer ignored the rules for VAT calculations and the documented precision limitations of money. TomTom but the point you are making about the possibility of misuse of this data type is an argument in favour of avoiding using it at all. Nathan No. I am pointing out that the argument given as a reason to never use it is basically incompetent developer, so the argument is bogus.
TomTom sadly there are many incompetent developers as well as many awesome ones and for me, unless I could guarantee how this data was going to be queried in the future and no-one was ever going to make the kind of mistakes described here, better to err on the side of caution and use a decimal type. That said, having read all these answers I can see there are some specific use cases where money would be an optimum type to use, I just wouldn't use it unless there was a very good use case for it e.
TomTom it isn't just the precision limitations. The internal representation can also cause issues. Money is a convenience type to catch ignorant developers, not a great type that developers are misusing. The vat example, regardless of the rules for calculating it, clearly should scare non-ignorant developers away when they apply the specific to the general.
Martin Smith Martin Smith k 80 80 gold badges silver badges bronze badges. How would you store bitcoin then? PanagiotisKanavos - I've no idea. I've never looked into bitcoin and know practically nothing about it! Link not working. Here is the link to the PDF download. See next Pro point Best for Money. MONEY is best for storing money and do operations, for example, in accounting. For very big accounting applications it is almost twice as fast , and it is extremely significant Low Precision of Money.
Money in real life doesn't need to be very precise. I mean, many people may care about 1 cent USD, but how about 0.
0コメント