July 24, 2018 at 7:04 am
sgmunson - Tuesday, July 24, 2018 6:42 AMHere you go again... choosing inappropriate data types for calculations and then using that as an excuse for using float. Sorry dude, but stop using BS examples. We can expect better from someone with your skillset.
Please show how to use appropriate data types.
I really want to see an example of it.
And please - don't call BS whatever does not fit your religious beliefs.
_____________
Code for TallyGenerator
July 24, 2018 at 7:10 am
Sergiy - Tuesday, July 24, 2018 6:46 AMYour example is not good.What you see is not the actual result of FLOAT type, but its DECIMAL (15, ?) representation.
FLOAT gives you 15 accurate digits in any result.
Nothing is measured to this level of precision in real life.
As another counter example:
-- b = 3.34, a = 1.22, and c = 2.28. The exact value of b2 - 4ac is .0292
Declare @a Float = 1.22
Declare @b-2 Float = 3.34
Declare @C Float = 2.28
FloatMath
----------------------
0.0292000000000012
July 24, 2018 at 7:21 am
akapellas - Wednesday, July 18, 2018 7:53 AMThank you guys, for your replies.So the best idea is to rewrite the same query in a different apporach?
Accuracy of FLOAT calculations is 15 digits.
Inaccuracy in 16th digit gives you negative argument for SQRT.
To prevent it from happening leave only accurate digits by rounding the arguument: set @klm = 6371 * ATAN(SQRT(round(1 - SQUARE(@Ans), 15)) / @Ans) -- No problem on this line anymore
_____________
Code for TallyGenerator
July 24, 2018 at 8:32 am
Thom A - Tuesday, July 24, 2018 2:04 AMThe problem there, however, isn't the decimal type itself, it's the precision and scale that has been chosen.
Heh... thanks Thom. Looks like I did nothing to prevent WW3 on this. I may have, in fact, added fuel to the fire.
You are absolutely correct in what you say above but the example I gave was just a 2 legged quick example of a problem with the Decimal data type. And, no... I'm not saying whether to use DECIMAL, NUMERIC, or FLOAT (I use one or the other based on the "It Depends" need and deal with the nuance of each when needed). I'm saying that you need to be aware of the gotcha's especially that 6 digit stuff to the right of the decimal point when doing multiple multiplications, etc. I used DECIMAL(38,x) to guarantee the example would show the symptoms of that. The real key is that when (for example), you have multiple multiplications, the effect becomes cumulative. Each successive multiplication follows the separate evaluation of "p1 + p2 + 1" and when that cumulative precision tops 32, that bloody "6 rule" comes into play. It's surprising how quickly you can go over that limit.
What's amazing to me is that MS does this forced 6 digit rounding when the precision hits a certain limit. That's very old technology that even a 4 function calculator beats (those calculate with 17 digits and display the result as 15 digits in most cases). It's also amazing that as long as math coprocessors have been out, MS (IMHO) didn't take advantage of them for this type of thing.
And, yes... I meant to use the NUMERIC datatype for the @N variables and forgot to correct the paste job. Thanks for the catch.
Anyway, I with you... I'm also going to bail on this discussion. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2018 at 8:52 am
Jeff Moden - Monday, July 23, 2018 9:22 PMSergiy - Monday, July 23, 2018 7:37 PMThom A - Wednesday, July 18, 2018 5:12 AMDon't use float, it's an inaccurate data type, that suffers rounding problems. If you use an accurate data type, such as decimal(12,10), the error doesn't occur, and it's more accurate. 🙂Stop repeating nonsense.
FLOAT computations are way more accurate than any of DECIMAL ones.Before world war 3 breaks out on this forum about FLOAT vs DECIMAL vs NUMERIC, here's a simple example of one of the many faults found with the DECIMAL and NUMERIC data types that Sergiy is correctly referring to. Run it and if you haven't read the article at https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-2017 , prepare for a surprise. 😀
DECLARE @D1 DECIMAL(38,17) = 0.123456789
,@D2 DECIMAL(38,17) = 10.0
,@N1 DECIMAL(38,17) = 0.123456789
,@N2 DECIMAL(38,17) = 10.0
,@F1 FLOAT = 0.123456789
,@F2 FLOAT = 10.0
;
SELECT DecimalResult = @D1*@D2
,NumericResult = @N1*@N2
,FloatResult = @F1*@F2;
Even Granny's 2 dollar calculator doesn't make that mistake so be careful when you calculate her interest payment on her mortgage using SQL Server because she'll get it right and you might not. 😀
It really comes down to knowing the domain of the values and how SQL Server works with precision and scale values, and how mathematics work. It is possible to use the DECIMAL (or NUMERIC) data types. The problem is many people don't take the time to learn and test.
DECLARE @D1 DECIMAL(38,17) = 0.123456789
,@D2 DECIMAL(38,17) = 10.0
,@N1 NUMERIC(38,17) = 0.123456789
,@N2 NUMERIC(38,17) = 10.0
,@F1 FLOAT = 0.123456789
,@F2 FLOAT = 10.0
;
SELECT DecimalResult = @D1*@D2
,NumericResult = @N1*@N2
,FloatResult = @F1*@F2;
GO
DECLARE @D1 DECIMAL(12,9) = 0.123456789
,@D2 DECIMAL(12,1) = 10.0
,@N1 NUMERIC(38,17) = 0.123456789
,@N2 NUMERIC(38,17) = 10.0
,@F1 FLOAT = 0.123456789
,@F2 FLOAT = 10.0
;
SELECT DecimalResult = @D1*@D2
,NumericResult = @N1*@N2
,FloatResult = @F1*@F2;
GO
July 24, 2018 at 8:53 am
SQL does take advantage of math coprocessors, that's what float/real are for. The fixed precision maths used by decimal has a different advantage: by rounding in a very specific way, with a number of digits, it's a lot more predictable. You always get the same kind of rounding, regardless of the numbers used, whereas with a true floating point arithmetic the level of inaccuracy in the results is highly dependent upon the exact numbers being used at the time (it can be better, it can be a lot worse) because both the variations in magnitude and the accuracy to which the mantissa can be represented make a big difference to the result. Better or Worse are highly situationally dependent and it's a lot more sensible to avoid stating absolutes about type usage.
And part of my day job is calculating mortgage payments, where this stuff really matters... 😉
July 24, 2018 at 8:58 am
andycadley - Tuesday, July 24, 2018 8:53 AMSQL does take advantage of math coprocessors, that's what float/real are for. The fixed precision maths used by decimal has a different advantage: by rounding in a very specific way, with a number of digits, it's a lot more predictable. You always get the same kind of rounding, regardless of the numbers used, whereas with a true floating point arithmetic the level of inaccuracy in the results is highly dependent upon the exact numbers being used at the time (it can be better, it can be a lot worse) because both the variations in magnitude and the accuracy to which the mantissa can be represented make a big difference to the result. Better or Worse are highly situationally dependent and it's a lot more sensible to avoid stating absolutes about type usage.And part of my day job is calculating mortgage payments, where this stuff really matters... 😉
To be sure, I was talking about the mess they made with the Decimal data type.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2018 at 9:07 am
Jeff Moden - Tuesday, July 24, 2018 8:58 AMTo be sure, I was talking about the mess they made with the Decimal data type.
Yeah, I just don't entirely agree it a mess. All non-integer maths is, in some way, flawed. It depends on what specific set of flaws work best for your current problem that determines which of the types you choose. Decimal is often entirely appropriate, Float equally appropriate in an entirely different set of circumstances. And very, very occasionally money might be the one you need (but probably not!)
July 24, 2018 at 9:17 am
andycadley - Tuesday, July 24, 2018 9:07 AMJeff Moden - Tuesday, July 24, 2018 8:58 AMTo be sure, I was talking about the mess they made with the Decimal data type.Yeah, I just don't entirely agree it a mess. All non-integer maths is, in some way, flawed. It depends on what specific set of flaws work best for your current problem that determines which of the types you choose. Decimal is often entirely appropriate, Float equally appropriate in an entirely different set of circumstances. And very, very occasionally money might be the one you need (but probably not!)
I guess we'll have to agree to disagree on the mess thing. I do agree that non-integer math will always have a limit and folks need to be aware of it but a tolerance build up to a precision of 32 automatically causing a reduction in scale to 6 (and some of the other nuances) is a real PITA. That's why I complained about not using the math coprocessor there. If it is used, they've overridden it.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2018 at 9:29 am
Jeff Moden - Tuesday, July 24, 2018 9:17 AMI guess we'll have to agree to disagree on the mess thing. I do agree that non-integer math will always have a limit and folks need to be aware of it but a tolerance build up to a precision of 32 automatically causing a reduction in scale to 6 (and some of the other nuances) is a real PITA. That's why I complained about not using the math coprocessor there. If it is used, they've overridden it.
If there's a mess, it's probably letting you specify quite such large values for scale - if you need to go much beyond 6 you're probably well into the territory where float is just better (or dealing with insanely huge number ranges that none of the provided types are good at). To be clear it doesn't use the co-processor for calculations with the decimal type, only float/real because math co-processors introduce the problems with true floating point math - as shown when you add a large number to a small decimal value - which decimal is designed to avoid.
July 24, 2018 at 9:46 am
Andy,
Decimal math is integer math.
The same thing.
Decimal number is an quantity of decimal shares.
Exact number - something you can count.
It's only legal to do decimal math when you add/substract, or multiply.
Division is only allowed when it fits whole-number operation.
As soon as there is a single non-whole-number division (like 1/3) you get an approximate number.
And you should not be using decimals anymore, time to switch to float.
Representing approximate values with exact data types always increases inaccuracy.
_____________
Code for TallyGenerator
July 24, 2018 at 10:20 am
Apart from the OP's problem already being solved with no relation to the majority of the subsequent thread, I think there is time for someone to write up the ever surfacing subject of Float vs Numeric
😎
As I stated earlier, float is the appropriate data type for the OP's problem, the float data type has the accuracy to calculate the fraction of a human hair when used for calculating geographical distances, adding financial type calculations into the works is totally irrelevant and does not add neither value nor accuracy to this thread :pinch:
July 24, 2018 at 8:18 pm
Sergiy - Tuesday, July 24, 2018 9:46 AMAndy,Decimal math is integer math.The same thing.Decimal number is an quantity of decimal shares.Exact number - something you can count.It's only legal to do decimal math when you add/substract, or multiply.Division is only allowed when it fits whole-number operation.As soon as there is a single non-whole-number division (like 1/3) you get an approximate number.And you should not be using decimals anymore, time to switch to float.Representing approximate values with exact data types always increases inaccuracy.
It sometimes does make me want to do things like we did in the old days... do everything with integers and then put the decimal point where it belongs.
"Shift Left, Load Zeros" 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
July 25, 2018 at 12:52 pm
Sergiy - Tuesday, July 24, 2018 6:53 AMsgmunson - Tuesday, July 24, 2018 6:20 AMSorry, but choosing your example based on the WRONG choice of data type is entirely inappropriate. You expect us to accept that representing a repeating decimal fraction such as 1/3 is appropriate for using the decimal data type ??? Seriously? And you want to claim this is appropriate because the "math" is so simple ??? You are certainly entitled to your opinion, but don't just expect everyone here to allow you the privilege of insulting our intelligence without consequence.My example shows that using DECIMAL data type for math operation of 2nd level is totally inappropriate.
DECIMAL calculations will always fail on 1/3, 1/7, 1/11, etc. Wherever you step out of INTEGERs world and enter the REALs world.
If you can prove otherwise - plase show it.
Will be much appreciated.
Yes, but you then used it as an example of what people would do, and that's not accurate. I'm not going to argue this any further with you either.. You don't get to play both sides of the fence, and since choice of data type is what is important here, I refuse to be lumped into the category of those consistently making a poor choice of same. You can have a nice day, but take your argumentative self and stick that where the sun don't shine.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 26, 2018 at 5:12 am
sgmunson - Wednesday, July 25, 2018 12:52 PMYes, but you then used it as an example of what people would do, and that's not accurate. I'm not going to argue this any further with you either.. You don't get to play both sides of the fence, and since choice of data type is what is important here, I refuse to be lumped into the category of those consistently making a poor choice of same. You can have a nice day, but take your argumentative self and stick that where the sun don't shine.
Yep. That's how people reply wen they have nothing to show to support their claims.
A bit of nonsense, a bit of personal attack, and (quite common in this thread) - " I'm not going to argue this any further".
Have a good day you too.
🙂
_____________
Code for TallyGenerator
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply