January 13, 2009 at 11:12 am
Scott T (12/30/2008)
For any math calculation avoid FLOAT use Decimal you will get Write result.:D
This is only true for Addition and Subtraction. For Multiplication and Division it is definitely not true, and in many cases, DECIMAL is likely to be a far worse choice that FLOAT.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 15, 2009 at 9:04 pm
Scott T (12/30/2008)
For any math calculation avoid FLOAT use Decimal you will get Write result.:D
DECLARE @One decimal, @Three decimal
SET @One = 1
SET @Three = 3
SELECT @One/@Three + @One/@Three + @One/@Three
Is it what you name "Write result" ?
:w00t:
Now, make them float and see the difference. 😛
_____________
Code for TallyGenerator
January 15, 2009 at 11:17 pm
Sergiy (1/15/2009)
Scott T (12/30/2008)
For any math calculation avoid FLOAT use Decimal you will get Write result.:D
DECLARE @One decimal, @Three decimal
SET @One = 1
SET @Three = 3
SELECT @One/@Three + @One/@Three + @One/@Three
Is it what you name "Write result" ?
:w00t:
Now, make them float and see the difference. 😛
Heh... I was waitin' for ya... knew you'd be along just about any minute. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2009 at 6:32 am
Agreed Jeff. Sergiy's example succinctly proves my point.
Thanks, Sergiy!
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 16, 2009 at 8:28 am
--With simple calculation we don't care about data-type (float or decimal)
--====================================
--When we start calculation like the following one, we should use decimal
-- Let's see example
--====================================
DECLARE @APR ??????? (18,3)
--------------------------------------
SET @APR=.074;
DECLARE @Months INT --how to break up the annual percentage
SET @Months=12;
DECLARE @Period INT -- (i.e. at what point in the future do you want to know the value)
SET @Period=36;
DECLARE @PMT DECIMAL(18,2) --monthly payment
SET @PMT=1889;
DECLARE @PV DECIMAL(18,2) --present value or starting point
SET @PV=132930;
DECLARE @EndPeriod INT --whether compounding occurs at the beginning or the end of the period
SET @EndPeriod=0;
DECLARE @FV DECIMAL(36,6)
SELECT @FV=(@PV*POWER(1+@APR/@Months,(@Period-@EndPeriod))
-@PMT*(POWER(1+@APR/@Months,@Period) -1)/(@APR/@Months))*POWER(1+@APR/@Months,@EndPeriod)
SELECT @FV
--=====================
--In Case When we declare with FLOAT
---------------------------------------
DECLARE @APR FLOAT
---------------------------------------
-- RESULT
---------------------------------------
89976.668128
--=====================
--=====================
--=====================
--In Case When we declare with DECIMAL
---------------------------------------
DECLARE @APR DECIMAL(18,3)
---------------------------------------
-- RESULT
---------------------------------------
89976.668104
--=====================
As you can see the results it's not matching. 😉 Well it's up to you know to make a decision.
-------------------------------------------------------------------
Only from my personal experience, if there is possibility for mistake it will happens, eventually.
-------------------------------------------------------------------
GOOD LUCK 🙂
January 16, 2009 at 10:00 am
Scott T (1/16/2009)
--With simple calculation we don't care about data-type (float or decimal)--====================================
--When we start calculation like the following one, we should use decimal
-- Let's see example
...
--=====================
As you can see the results it's not matching...
Scott, what makes you think that the DECIMAL answer is more correct than the FLOAT answer?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 17, 2009 at 2:19 am
Scott T (1/16/2009)
As you can see the results it's not matching. 😉 Well it's up to you know to make a decision.-------------------------------------------------------------------
In science there is no space for someone's decisions.
You need to find the proof.
To see the proof just look where @APR is used.
First calculation:
@APR/@Months
What are the results for DECIMAL and FLOAT options?
Change the final SELECT to see it:
SELECT 1+@APR/@Months, @FV
DECIMAL option returns .00616666666666
FLOAT option returns 6.1666666666666667E-3
Anybody who was actually studying math in school will say that FLOAT result is more accurate.
DECIMAL option introduces bigger error on the first step. And this error is being multiplied by every further calculation.
Of course, results don't match - DECIMAL calculation makes an error on the first step, and there is nothing to correct that error further down the track.
Does it mean that FLOAT calculations don't introduce errors at all?
No.
There is no way to avoid systematic errors in computer calculations. Just because of limited precision of any number stored in computer memory.
But!
FLOAT calculation will be always more precise than any of DECIMAL calculations of the same deepness.
Deepness - it's the number of bits you reserve for storing the numbers during calculations.
This is exactly the reason why float point calculations only are allowed in scientific calculations where getting precise result is crucial and amateurism is not welcome.
Now, back to your case:
FLOAT carries and uses in calculations 15 digits of a number.
Because of the systematic errors it loses 1 digit of precision after 2-4 calculations.
In your formula it's not correct to trust 14th digit, so only 13 digits of the result can be considered correct.
You take only 11 digit for the final number, so I can state all of them are correct.
Unlike DECIMAL one which makes an error in 12th digit on the first step, shifts it to 10th digit 2 steps later and keeps to make it worse all the way.
Is there enough ground for a decision? 😎
_____________
Code for TallyGenerator
January 17, 2009 at 9:12 am
For those who may care, the formal study of numerical approximations and error in calculations is called "Numerical Analysis". It's a course that Math & CompSci majors should take in college. It's been 30 years since I took it and I am admittedly rusty, but Sergiy is obviously still fairly adept at it. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 17, 2009 at 1:32 pm
Don't, even for a second, think that I'm trying to fan any flames here. 🙂 I'm only interested in knowing because you guys have probably forgotten more about precision than I'll ever know. 😉
If I were doing mortgage calculations, and let's assume that the max limit on the mortgage were, say, ummm... a penny less than a billion dollars... which would be better to use so far as accuracy goes...
FLOAT or DECIMAL (38,27)
--Jeff Moden
Change is inevitable... Change for the better is not.
January 17, 2009 at 3:43 pm
Jeff Moden (1/17/2009)
If I were doing mortgage calculations, and let's assume that the max limit on the mortgage were, say, ummm... a penny less than a billion dollars... which would be better to use so far as accuracy goes...FLOAT or DECIMAL (38,27)
1. If you see a mortgage for a billion dollars then there must be a lawyer involved. Very good one. And very smart one. So you'd better be precise. A smallest error in favor of either side (if he's a really smart one ;-)) could cost you a million or two.
2. Billion holds 9 digits, penny adds other 2. Totally 11 digits precision required. FLOAT should be enough.
3. DECIMAL (38, 27) uses 64 bit calculations, SQL version of FLOAT is 32 bit. I guess there is no need to explain further. 🙂
_____________
Code for TallyGenerator
January 17, 2009 at 3:55 pm
Heh, I did say "likely", Jeff. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 17, 2009 at 4:11 pm
Sergiy (1/17/2009)
Jeff Moden (1/17/2009)
If I were doing mortgage calculations, and let's assume that the max limit on the mortgage were, say, ummm... a penny less than a billion dollars... which would be better to use so far as accuracy goes...FLOAT or DECIMAL (38,27)
1. If you see a mortgage for a billion dollars then there must be a lawyer involved. Very good one. And very smart one. So you'd better be precise. A smallest error in favor of either side (if he's a really smart one ;-)) could cost you a million or two.
2. Billion holds 9 digits, penny adds other 2. Totally 11 digits precision required. FLOAT should be enough.
3. DECIMAL (38, 27) uses 64 bit calculations, SQL version of FLOAT is 32 bit. I guess there is no need to explain further. 🙂
Thanks ol' friend.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 28, 2009 at 9:45 am
complement of function return payment of being or end
select @PV*Power((@apr+1),@nper)*@apr/(Power((@apr+1),@nper)-1); payment end
select (@pv*Power((@apr+1),@nper)*@apr/(Power((@apr+1),@nper)-1))/(@apr+1); bein of payment
March 25, 2009 at 8:49 am
Point taken on the float bit. In your example one does get the correct result, HOWEVER:
DECLARE @a AS FLOAT
SET @a = 1
SELECT @a
--VS
DECLARE @a AS DECIMAL
SET @a = 1
SELECT @a
For me, that's an obvious deal breaker.
my rule of thumb:
NEVER use float, unless your smart enought to know when it's going to work right, because most of the time it wont.
March 25, 2009 at 8:55 am
Sergiy (1/15/2009)
Scott T (12/30/2008)
For any math calculation avoid FLOAT use Decimal you will get Write result.:D
DECLARE @One decimal, @Three decimal
SET @One = 1
SET @Three = 3
SELECT @One/@Three + @One/@Three + @One/@Three
Is it what you name "Write result" ?
:w00t:
Now, make them float and see the difference. 😛
Point taken on the float bit. In your example one does get the correct result, HOWEVER:
DECLARE @a AS FLOAT
SET @a = 1
SELECT @a
--VS
DECLARE @a AS DECIMAL
SET @a = 1
SELECT @a
For me, that's an obvious deal breaker.
my rule of thumb:
NEVER use float, unless your smart enought to know when it's going to work right, because most of the time it wont.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply