October 19, 2009 at 7:20 pm
I am designing a table with lot of dollar values. What are the pros/cons to use decimal Vs money datatype in SS2005?
October 19, 2009 at 8:15 pm
There is a lot of debate on the issue. Here's a link to a post by the SQLCAT team, http://sqlcat.com/technicalnotes/archive/2008/09/25/the-many-benefits-of-money-data-type.aspx and here's one to another thread here on SSC, http://www.sqlservercentral.com/Forums/Topic544518-9-1.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 22, 2009 at 1:04 pm
Thx, it is a good discussion.
Is Microsoft planning to drop any of the decimal related data types in the future releases of SQL Servers?
October 22, 2009 at 2:37 pm
No, Microsoft isn't planning on dumping any of these data types.
One of the things that gets left out of most of these debates is that the CPU does all it's non-whole-number math on floating point numbers, and then converts them back and forth between other data types. So, you can pretend you're avoiding floating point rounding errors by using decimal or money, but you really aren't. This really only matters if you will multiply or divide at any point, but very few financial calculations don't end up including one of those somewhere along the way.
In the linked discussion, for example, someone points out that you wouldn't multiple money by money, you'd multiply money by a decimal value. Completely meaningless statement. First, SQL Server will have to do an implicit convert/cast (or you'll have to do one explicitly) in order to do the multiplication. Second, the CPU will do the actual math on a set of floating point numbers. The point the statement made was semi-valid in that you wouldn't, in the real world, multiply dollars times dollars and get dollars-squared as your unit. Beyond the semantics, the statement was misleading and pointless.
The only reason decimal multiplication seems more accurate than floating point multiplication is because clever engineers have made the floating point op in the decimal multiplication error-correct in ways that hide the issue from you.
Just take that into account when you decide which to use. I use floating point numbers for money, and just take necessary steps to make sure precision does not exceed accuracy.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 23, 2009 at 7:22 am
GSquared
Thanks for sharing several 'behind-the-scenes' tips on this topic.
Is there a MS doc that explains how their algorithms work for different operations/types?
Dan
October 23, 2009 at 2:23 pm
Some of it's cooked into the CPU. Some might also be Microsoft. I'd hit Google/Bing/whatever to look for details.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 23, 2009 at 3:33 pm
can you explain what do you mean by 'cooked into the CPU'
October 26, 2009 at 7:08 am
The CPU includes a floating point processor. Used to be called a math-coprocessor back in the 286, 386 days, before Pentium and Core 2 and Core i7 and whatever's next. Now it's just part of thing. That processor has some of the numerical conversion code built into it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 28, 2009 at 9:09 am
Gus
1)
When you use FP numbers for money values, do you use convert statements to trim the scale - is this what mean by 'take necessary steps to make sure precision does not exceed accuracy.'. If it is so, how do you justify the overhead of the convert statements?
(example: SELECT convert decimal(4,2),@Amount_in_Float)) in cases like where the Float value returns an amount like 39.99999, which is why many seem to avoid float in the first place for currencies.. (
http://www.sqlexpert.org/why-you-should-not-use-float-datatype-for-financial-data )
2)
Can you please explain your point "you can pretend you're avoiding floating point rounding errors by using decimal or money, but you really aren't. This really only matters if you will multiply or divide"
3)
What I got from your statement
"
The only reason decimal multiplication seems more accurate than floating point multiplication is because clever engineers have made the floating point op in the decimal multiplication error-correct in ways that hide the issue from you.
"
is that Float makes decimal looks good, while the credit should go to FP. Am I reading it correct?
4)
Can you please comment on the attached script: even though there is conversion on all the data types for multiplication,implicitly, why is that the results differ and sometimes inconsistent? It seems to be hard to say which is right & reliable, at most times. Thanks much - Dan.
DECLARE
@D1 DECIMAL,@D2 DECIMAL ,@D3 DECIMAL(30,6),
@N1 NUMERIC , @N2 NUMERIC ,@N3 NUMERIC,
@M1 MONEY,@M2 MONEY ,@M3 MONEY,
@F1 FLOAT , @F2 FLOAT ,@F3 FLOAT
--Random Numbers for Multiplication
SELECT @M1 = 5244.11,@M2 = 649164.029595,@M3 = 57234.22,@F1 = 5244.11,@F2 = 649164.029595,@F3 = 57234.22,@D1 = 5244.11,@D2 = 649164.029595,@D3 = 57234.22,@N1 = 5244.11,@N2 = 649164.029595,@N3 = 57234.22
--Multiply just each datatype with its own type
SELECT
(@M1 * (@M3 * @M2)) as "MultiplyM",
(@D1 * (@D3 * @D2)) as "MultiplyD",
(@F1 * (@F3 * @F2)) as "MultiplyF",
(@N1 * (@N3 * @N2)) as "MultiplyN",
@M1 * @M3 * @M2 as "MultiplyM_No_Parenthesis" ,
@D1 * @D3 * @D2 as "MultiplyD_No_Parenthesis",
@F1 * @F3 * @F2 as "MultiplyF_No_Parenthesis",
@N1 * @N3 * @N2 as "MultiplyN_No_Parenthesis"
--DIfferences between the different data types (Some are off big time)
SELECT
(@M1 * (@M3 * @M2))-(@D1 * (@D3 * @D2)) as "Money > Decimal by",
(@M1 * (@M3 * @M2))-(@F1 * (@F3 * @F2)) as "Money > Float by",
(@M1 * (@M3 * @M2))-(@N1 * (@N3 * @N2)) as "Money > Numeric by",
(@M1 * (@M3 * @M2))-@M1 * @M3 * @M2 as "Money_Parenthesis > Money_No_Parenthesis",
(@D1 * (@D3 * @D2))-@D1 * @D3 * @D2 as "Decimal_Parenthesis > Decimal_No_Parenthesis",
(@F1 * (@F3 * @F2))-@F1 * @F3 * @F2 as "Float_Parenthesis > Float_No_Parenthesis",
(@N1 * (@N3 * @N2))-@N1 * @N3 * @N2 as "Numeric_Parenthesis > Numeric_No_Parenthesis"
--Multiply same numbers with different data types
SELECT
(@D1 * (@F3 * @N2)) as "Multiply_DFN",
(@D1 * (@N3 * @F2)) as "Multiply_DNF",
(@D1 * (@F3 * @N2))-(@F1 * (@D3 * @N2)) as "Multiply_DFN-Multiply_DNF",
(@F1 * (@D3 * @N2)) as "Multiply_FDN",
(@F1 * (@N3 * @D2)) as "Multiply_FND",
(@F1 * (@D3 * @N2))-(@F1 * (@N3 * @D2)) as "Multiply_FDN-Multiply_FND",
(@N1 * (@D3 * @F2)) as "Multiply_NDF",
(@N1 * (@F3 * @D2)) as "Multiply_NFD",
(@N1 * (@D3 * @F2))-(@N1 * (@F3 * @D2)) as "Multiply_NDF-Multiply_NFD"
October 29, 2009 at 7:06 am
1. I generally prefer to let the presentation layer do the presentation. That means set the web page or application to display money, and it'll do so. This also means that it can follow local conventions when it comes to things like commas vs periods in large numbers, and so on. All that, including rounding, gets done at once.
2. If you multiply two decimal numbers together, the first thing the CPU does is change them into binary representations of floating point numbers, then it multiplies them. Same for division. Any attempt to bypass this means you have to use something other than a binary computer, which means you'd better pull out an abacus. That's how it works at a mechanical level. You can't get past the fact that the computer is binary. Decimal numbers are a way to pretend it's not, nothing more.
3. See number 2 above. When you multiply decimal numbers (or divide them), clever engineering throws up smoke and mirrors and makes it look like something it isn't.
4. All I can say is, you have to watch out when you process numbers, because precision can easily exceed accuracy. Any time it does, you're violating basic principles of mathematics.
I keep bringing up precision exceeding accuracy, because that's the mathematical principle here. If you aren't familiar with the concept, look it up in a high school or college level text (whatever you're more comfortable with). If I remember correctly, there were chapters on the subject in my high school physics and calculus texts. It's a critical piece of knowledge if you ever deal with numbers, or engineering, or science of any sort.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 29, 2009 at 11:28 am
Gus,
I like your approach on #1. Excellent!
On #2 & 3, you mention that all decimals are converted to FP for internal calculation; my question in #4 is why then the results vastly different between the same numbers, with different data types.
In the example displayed in the previous post, multiplying 3 numbers
5244.11 * 649164.029595 * 57234.22
Produced 194837648387267.52 for the decimal type, and 194841744253457(15 precision) for Float data type.
The difference is 4,095,866,189.48 (Float is greater by 4 billion).
Internally, decimal * decimal is float*float, right. So should not the results be at least close?
What am I missing to see?
Also, can you kindly give a small example of 'precision exceeding accuracy', so I will know how to further look it up.
Thanks
Dan
October 29, 2009 at 12:13 pm
What you're running into there is exactly precision exceeding accuracy.
The first number, 5244.11, has 5 "significant digits". The final 1 could be rounded or could be exact, there's no way to know from the data presented. Thus, your accuracy is limited to 5 digits. The other numbers both have more digits, and are thus more "precise". Any math going beyond the first five digits is actually a total waste of effort, unless you can prove that 5244.11 is actually 5244.110 exactly.
I'd start off with the wikipedia article on accuracy and precision, then the one on significant figures, and then go from there if you need to.
Here's an example: Take your body temperature with a standard mercury thermometer. Let's say, for example, that the mercury is pretty close to the line for 98.6 F, which is a normal temperature. Now, let's say we measure my temperature, and it comes with the mercury somewhere between 96.8 and 96.9, a little below average, so we call it 96.8 because it looks closer to that. Would it make sense to say that the average temperature in this case was 97.700000? No? Then try this:
select (98.6+96.8)/2;
This is a case of taking the number too far. We're saying the average is accurate to 6 decimal places, off of an eyeball measurement of some mercury being close to some lines on a thermometer. Our precision (number of figures) is WAY out of wack with our accuracy ("looks closer to 96.8 than 96.9 to me, how about you?").
Does that behavior go away if we're just operating to the left of the decimal places? Not at all. In that case, we'd have your temperature rounded to 99 and mine to 97, and we'd get an average of 98. But is that exactly 98? No, we already know that it's actually just about 3 tenths of a degree below that from the prior math.
Precise values exist only in the human imagination. The real world doesn't deal in them. Ever compare two actual apples to see if they were truly identical? Do it, and you'll quickly find that comparing apples to apples is only possible in aggregate, not in precise details.
Does that help?
Your example code shows the problems that the engineers who build CPUs have to deal with, and the problems that software engineers have to deal with, when working with complex numbers. The move from the decimal representation that we see to the binary representation that is the only thing possible for the CPU to deal with, is error prone. There's really no way around that. The thing to do is take accuracy and precision into account in your operations, and you'll be fine. If you know how they work, and how significant digits work, you can deal with it. If you don't, expect your system to give you funky math.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 30, 2009 at 7:05 am
G2,
Decimal and money are fixed-point types. They don't go near an FPU 🙂
http://blogs.msdn.com/khen1234/archive/2005/05/13/417153.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 30, 2009 at 7:20 am
Paul White (10/30/2009)
G2,Decimal and money are fixed-point types. They don't go near an FPU 🙂
http://blogs.msdn.com/khen1234/archive/2005/05/13/417153.aspx
The problem isn't the FPU, the problem is the binary translation, and the limits of accuracy and precision.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2009 at 9:20 pm
Gus
According to wiki, 5244.11 has 6 significant digits, not? The 3 rules for SD are:
1. All non-zero digits are significant.
2. In a number without a decimal point, only zeros BETWEEN non-zero digits are significant (unless a bar indicates the last significant digit--see below).
3. In a number with a decimal point, all zeros to the right of the left-most non-zero digit are significant.
http://en.wikipedia.org/wiki/Significant_figures
Thanks for the good thermometer & apple examples.
Dan
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply