July 23, 2018 at 7:37 pm
Thom 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.
_____________
Code for TallyGenerator
July 23, 2018 at 9:22 pm
Sergiy - 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.
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
--Jeff Moden
Change is inevitable... Change for the better is not.
July 24, 2018 at 2:04 am
Jeff Moden - Monday, July 23, 2018 9:22 PMBefore 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. 😀
The problem there, however, isn't the decimal type itself, it's the precision and scale that has been chosen. Kind of like if you have a 2 varchar(60)'s, and try to put the concatenated values into a varchar(100), some truncation is going to occur (in this case there's rounding as you've used a huge precision for a small number, and thus scale is lost). The important thing is that a appropriate datatype is chosen; a decimal (38,17) is an awful choice for the value 10.0. If you chose appropriate scale and precision then it isn't an issue (which was one of my points in my original post): DECLARE @D1 DECIMAL(38,17) = 0.123456789
,@D2 DECIMAL(38,17) = 10.0
,@N1 NUMERIC(38,17) = 0.123456789 --I assume you meant NUMERIC here, Jeff, as you called it @N1
,@N2 NUMERIC(38,17) = 10.0 --I assume you meant NUMERIC here, Jeff, as you called it @N2
,@N3 NUMERIC(10,9) = 0.123456789
,@N4 NUMERIC(3,1) = 10.0
,@F1 FLOAT = 0.123456789
,@F2 FLOAT = 10.0
;
SELECT DecimalResult = @D1*@D2 --1.234568 (Not quite correct)
,NumericResult = @N1*@N2 -- 1.234568 (Not quite correct)
,FloatResult = @F1*@F2 -- 1.23456789 (correct)
,NumericResult = @N3*@N4; --1.2345678900 (correct)
If we go the other way though, we start to see the problem as well (the float being the worst):DECLARE @D1 DECIMAL(38,17) = 123456789012345.0
,@D2 DECIMAL(38,17) = 10.12345678
,@N1 NUMERIC(38,17) = 123456789012345.0
,@N2 NUMERIC(38,17) = 10.12345678
,@N3 NUMERIC(16,1) = 123456789012345.0
,@N4 NUMERIC(10,8) = 10.12345678
,@F1 FLOAT = 123456789012345.0
,@F2 FLOAT = 10.12345678
;
SELECT DecimalResult = @D1*@D2 --1249809467764053.493949 (not quite correct)
,NumericResult = @N1*@N2 --1249809467764053.493949 (not quite correct)
,FloatResult = @F1*@F2 --1.24980946776405E+15 = 1249809467764050 (very wrong)
,NumericResult = @N3*@N4; --1249809467764053.493949100 (Correct)
To repeat myself, here float is inappropriate. For what the OP is doing, float is also inaccurate as it is inappropriate; just like Jeff's choice of using a scale of 38 or store a number with a scale of 3.
Sergiy - 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.
To quote the documentation:
Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. The ISO synonym for real is float(24).
Underlining my own.
I don't mind having things pointing out if I'm wrong; learning from your mistakes is an invaluable tool, especially in this industry. Perhaps, in my initial post, I should have stated "approximate" (instead of "inaccurate"), however, if you're going to call my post "nonsense", have the courtesy to try and back up your statement. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 24, 2018 at 2:37 am
On the float vs numeric, for the purpose of calculating geographical distances, float is the correct data type IF the correct calculation methods are used, which by the way, expect the input coordinates to be of the type float.
😎
July 24, 2018 at 5:24 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.Sergiy - Monday, July 23, 2018 7:37 PM
Sorry, that's totally wrong.
Here is the simple proof.
So simple you could not possibly miss it in primary school.
Share a whole piece by 3 and then bring the 3 shares back together.
According to the math of any level it must be exactly the whole thing back in the end.
Let's see how it works with different data types:
declare @DV decimal (12,10), @DN decimal (12,10)
declare @DV_big decimal (38,30), @DN_big decimal (38,30)
declare @FV FLOAT, @FN FLOATselect @DV = 1, @DN = 3
select @DV_big = 1, @DN_big = 3
select @FV = 1, @FN = 3
select @DV = @DV/@DN
select @DV = @DV+@DV+@DV
select @DV_big = @DV_big/@DN_big
select @DV_big = @DV_big+@DV_big+@DV_big
select @FV = @FV/@FN
select @FV = @FV+@FV+@FV
select @DV [Decimal 1], @DV_big [Decimal_big 1], @FV [Float 1]
Decimal 1 Decimal_big 1 Float 1
--------------------------------------- --------------------------------------- ----------------------
0.9999999999 0.999999000000000000000000000000 1
Impressive, ha?
So many useless zeros at the end of so inaccurate result.
Feel free to choose any other precision and scale to make the decimal result look more accurate.
_____________
Code for TallyGenerator
July 24, 2018 at 5:36 am
Sergiy - Tuesday, July 24, 2018 5:24 AMSorry, that's totally wrong.
Here is the simple proof.
So simple you could not possibly miss it in primary school.Share a whole piece by 3 and then bring the 3 shares back together.
According to the math of any level it must be exactly the whole thing back in the end.
Let's see how it works with different data types:declare @DV decimal (12,10), @DN decimal (12,10)
declare @DV_big decimal (38,30), @DN_big decimal (38,30)
declare @FV FLOAT, @FN FLOATselect @DV = 1, @DN = 3
select @DV_big = 1, @DN_big = 3
select @FV = 1, @FN = 3select @DV = @DV/@DN
select @DV = @DV+@DV+@DVselect @DV_big = @DV_big/@DN_big
select @DV_big = @DV_big+@DV_big+@DV_bigselect @FV = @FV/@FN
select @FV = @FV+@FV+@FVselect @DV [Decimal 1], @DV_big [Decimal_big 1], @FV [Float 1]
Decimal 1 Decimal_big 1 Float 1
--------------------------------------- --------------------------------------- ----------------------
0.9999999999 0.999999000000000000000000000000 1
Impressive, ha?
So many useless zeros at the end of so inaccurate result.Feel free to choose any other precision and scale to make the decimal result look more accurate.
Thanks Sergiy, that's a much more informative post. With that, I do agree that there is a concern. In fact, if you use a decimal(1,0), the returned value is 0 (as it's effectively doing integer maths). As shown above, however, I wouldn't suggest that float is specifically more accurate (or inaccurate) then, as then is definitely a "depends" here.
The specific point here, however, is that with the way the OP is doing their expression, float does result in the wrong answer, however, decimal (with an appropriate scale and precision) does. Eirikur, however, has expressed that if they use the correct tools (functions) for the job then that issue should be result. I've not had to deal with geographical data on SQL Server, so not something I can comment on though.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 24, 2018 at 5:57 am
Sergiy - Tuesday, July 24, 2018 5:24 AMThom 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.Sergiy - Monday, July 23, 2018 7:37 PMSorry, that's totally wrong.
Here is the simple proof.
So simple you could not possibly miss it in primary school.Share a whole piece by 3 and then bring the 3 shares back together.
According to the math of any level it must be exactly the whole thing back in the end.
Let's see how it works with different data types:declare @DV decimal (12,10), @DN decimal (12,10)
declare @DV_big decimal (38,30), @DN_big decimal (38,30)
declare @FV FLOAT, @FN FLOATselect @DV = 1, @DN = 3
select @DV_big = 1, @DN_big = 3
select @FV = 1, @FN = 3select @DV = @DV/@DN
select @DV = @DV+@DV+@DVselect @DV_big = @DV_big/@DN_big
select @DV_big = @DV_big+@DV_big+@DV_bigselect @FV = @FV/@FN
select @FV = @FV+@FV+@FVselect @DV [Decimal 1], @DV_big [Decimal_big 1], @FV [Float 1]
Decimal 1 Decimal_big 1 Float 1
--------------------------------------- --------------------------------------- ----------------------
0.9999999999 0.999999000000000000000000000000 1
Impressive, ha?
So many useless zeros at the end of so inaccurate result.Feel free to choose any other precision and scale to make the decimal result look more accurate.
You can prove anything with edge cases:
select
cast(10000000 as float)+CAST(0.000000001 as float) As FloatResult,
cast(10000000 as decimal(38,10))+CAST(0.000000001 as decimal(38,10)) As DecimalResult
gives
FloatResult DecimalResult
---------------------- ---------------------------------------
10000000 10000000.0000000010
For which Float is clearly less correct than decimal.
As a datatype, float is exceptionally good for very small values OR very large values, but runs into all sorts of exciting issues if you mix and match the two. It does have a whole raft of special case logic for dealing with issues like 1/3 and is implemented in hardware and the processor level so is really, really fast. Decimal on the other hand is much better at dealing with a broader range of numbers but does require you to be more careful if you perform extensive maths on it because rounding errors near the limits of Precision/Scale can become cumulative without too much effort (as amply demonstrated).
Right tool for the right job, no silver bullets, no sacred cows...etc...
July 24, 2018 at 6:20 am
Sergiy - Tuesday, July 24, 2018 5:24 AMThom 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.Sergiy - Monday, July 23, 2018 7:37 PMSorry, that's totally wrong.
Here is the simple proof.
So simple you could not possibly miss it in primary school.Share a whole piece by 3 and then bring the 3 shares back together.
According to the math of any level it must be exactly the whole thing back in the end.
Let's see how it works with different data types:declare @DV decimal (12,10), @DN decimal (12,10)
declare @DV_big decimal (38,30), @DN_big decimal (38,30)
declare @FV FLOAT, @FN FLOATselect @DV = 1, @DN = 3
select @DV_big = 1, @DN_big = 3
select @FV = 1, @FN = 3select @DV = @DV/@DN
select @DV = @DV+@DV+@DVselect @DV_big = @DV_big/@DN_big
select @DV_big = @DV_big+@DV_big+@DV_bigselect @FV = @FV/@FN
select @FV = @FV+@FV+@FVselect @DV [Decimal 1], @DV_big [Decimal_big 1], @FV [Float 1]
Decimal 1 Decimal_big 1 Float 1
--------------------------------------- --------------------------------------- ----------------------
0.9999999999 0.999999000000000000000000000000 1
Impressive, ha?
So many useless zeros at the end of so inaccurate result.Feel free to choose any other precision and scale to make the decimal result look more accurate.
Sorry, 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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 24, 2018 at 6:35 am
Thom A - Tuesday, July 24, 2018 2:04 AMIf you chose appropriate scale and precision then it isn't an issue (which was one of my points in my original post):DECLARE @D1 DECIMAL(38,17) = 0.123456789
,@D2 DECIMAL(38,17) = 10.0
,@N1 NUMERIC(38,17) = 0.123456789 --I assume you meant NUMERIC here, Jeff, as you called it @N1
,@N2 NUMERIC(38,17) = 10.0 --I assume you meant NUMERIC here, Jeff, as you called it @N2
,@N3 NUMERIC(10,9) = 0.123456789
,@N4 NUMERIC(3,1) = 10.0
,@F1 FLOAT = 0.123456789
,@F2 FLOAT = 10.0
;
SELECT DecimalResult = @D1*@D2 --1.234568 (Not quite correct)
,NumericResult = @N1*@N2 -- 1.234568 (Not quite correct)
,FloatResult = @F1*@F2 -- 1.23456789 (correct)
,NumericResult = @N3*@N4; --1.2345678900 (correct)If we go the other way though, we start to see the problem as well (the float being the worst):
DECLARE @D1 DECIMAL(38,17) = 123456789012345.0
,@D2 DECIMAL(38,17) = 10.12345678
,@N1 NUMERIC(38,17) = 123456789012345.0
,@N2 NUMERIC(38,17) = 10.12345678
,@N3 NUMERIC(16,1) = 123456789012345.0
,@N4 NUMERIC(10,8) = 10.12345678
,@F1 FLOAT = 123456789012345.0
,@F2 FLOAT = 10.12345678
;
SELECT DecimalResult = @D1*@D2 --1249809467764053.493949 (not quite correct)
,NumericResult = @N1*@N2 --1249809467764053.493949 (not quite correct)
,FloatResult = @F1*@F2 --1.24980946776405E+15 = 1249809467764050 (very wrong)
,NumericResult = @N3*@N4; --1249809467764053.493949100 (Correct)
To repeat myself, here float is inappropriate. For what the OP is doing, float is also inaccurate as it is inappropriate; just like Jeff's choice of using a scale of 38 or store a number with a scale of 3.
My mom always taught me to verify the correctness of my calculations with reverse operations.
So, let's see how accurate and trustful are your claims of correct and wrong calculations in the example above
DECLARE @D1 DECIMAL(38,17) = 123456789012345.0
,@D2 DECIMAL(38,17) = 10.12345678
,@N1 NUMERIC(38,17) = 123456789012345.0
,@N2 NUMERIC(38,17) = 10.12345678
,@N3 NUMERIC(16,1) = 123456789012345.0
,@N4 NUMERIC(10,8) = 10.12345678
,@F1 FLOAT = 123456789012345.0
,@F2 FLOAT = 10.12345678
;
SELECT @D1 = @D1*@D2
,@N1 = @N1*@N2
,@F1 = @F1*@F2
,@N3 = @N3*@N4;
SELECT [@D1] = @D1/@D2 --
,[@N1] = @N1/@N2 --
,[@F1] = @F1/@F2 --
,[@N3] = @N3/@N4; --
OK, straight away, numeric calculations with @N3 and @N4 are out:Msg 8115, Level 16, State 8, Line 15
Arithmetic overflow error converting numeric to data type numeric.
Why?
Because of this: SELECT SQL_VARIANT_PROPERTY ( @N3*@N4, 'precision') -- 27
, SQL_VARIANT_PROPERTY ( @N3*@N4, 'scale') -- 9
, SQL_VARIANT_PROPERTY ( @N3*@N4, 'TotalBytes'); -- 17
As you can see, the product is not of the same scale and precision as the original values defined by you.
Too keep the calculation accurate SQL Server has to add up precisions and scales of original decimals:
new p = p1 + p2 + 1
new s = s1 +s2
And, consequently, it double the amount of storage required for the value.
But let's assume we have the gift of predicting which scales and precisions we're gonna need for our intermediate results and add another variable:DECLARE @D1 DECIMAL(38,17) = 123456789012345.0
,@D2 DECIMAL(38,17) = 10.12345678
,@N1 NUMERIC(38,17) = 123456789012345.0
,@N2 NUMERIC(38,17) = 10.12345678
,@N3 NUMERIC(16,1) = 123456789012345.0
,@N4 NUMERIC(10,8) = 10.12345678
,@N5 NUMERIC(27,9)
,@F1 FLOAT = 123456789012345.0
,@F2 FLOAT = 10.12345678
;
SELECT @D1 = @D1*@D2
,@N1 = @N1*@N2
,@F1 = @F1*@F2
,@N5 = @N3*@N4;
SELECT @D1 = @D1/@D2 --
,@N1 = @N1/@N2 --
,@F1 = @F1/@F2 --
,@N3 = @N5/@N4; --
SELECT @D1 = @D1/@D2 --
,@N1 = @N1/@N2 --
,@F1 = @F1/@F2 --
,@N3 = @N5/@N4; --
SELECT [@D1] = @D1 -- 123456789012344.99999900000000000 - a bit off
,[@N1] = @N1 -- 123456789012344.99999900000000000 - a bit off
,[@F1] = @F1 -- 123456789012345 - accurate
,[@N3] = @N3 -- 123456789012345.0 - accurate
So, big decimals clearly fail, float is 100% accurate, as well as smaller decimal.
But keep in mind - to keep up with float accuracy the"smaller" decimal had to use twice as much memory and 4 times as much CPU clocks.
Why "bigger" decimal could not keep up with the accuracy?
Well, to do so it would need to add up precisions and scales of original values, but it exceeded the maximum precision of 38.
So, it has to sacrifiy on scale to prevent aritmetic overflow.
And now - the cherry on the cake.
It's becomes so clear how to break the "accuracy" of "appropriate" decimals.
Just add another operation, which will throw it over the precision limitations for DECIMAL data type:
DECLARE @D1 DECIMAL(38,17) = 123456789012345.0
,@D2 DECIMAL(38,17) = 10.12345678
,@N1 NUMERIC(38,17) = 123456789012345.0
,@N2 NUMERIC(38,17) = 10.12345678
,@N3 NUMERIC(16,1) = 123456789012345.0
,@N4 NUMERIC(10,8) = 10.12345678
,@N5 NUMERIC(14,8) = 1000.12345678
,@F1 FLOAT = 123456789012345.0
,@F2 FLOAT = 10.12345678
,@F3 FLOAT = 1000.12345678
;
SELECT DecimalResult = @D1*@D2*@D2/@D2/@D2 -- 123456789012344.999999
,NumericResult = @N1*@N2*@N2/@N2/@N2 -- 123456789012344.999999
,FloatResult = @F1*@F2*@F3/@F2/@F3 -- 123456789012345
,NumericResult = @N3*@N4*@N5/@N4/@N5; -- 123456789012344.999999
At the end - FLOAT wins in accuracy.
Float always wins in accuracy.
_____________
Code for TallyGenerator
July 24, 2018 at 6:42 am
Sergiy - Tuesday, July 24, 2018 6:35 AMThom A - Tuesday, July 24, 2018 2:04 AMIf you chose appropriate scale and precision then it isn't an issue (which was one of my points in my original post):DECLARE @D1 DECIMAL(38,17) = 0.123456789
,@D2 DECIMAL(38,17) = 10.0
,@N1 NUMERIC(38,17) = 0.123456789 --I assume you meant NUMERIC here, Jeff, as you called it @N1
,@N2 NUMERIC(38,17) = 10.0 --I assume you meant NUMERIC here, Jeff, as you called it @N2
,@N3 NUMERIC(10,9) = 0.123456789
,@N4 NUMERIC(3,1) = 10.0
,@F1 FLOAT = 0.123456789
,@F2 FLOAT = 10.0
;
SELECT DecimalResult = @D1*@D2 --1.234568 (Not quite correct)
,NumericResult = @N1*@N2 -- 1.234568 (Not quite correct)
,FloatResult = @F1*@F2 -- 1.23456789 (correct)
,NumericResult = @N3*@N4; --1.2345678900 (correct)If we go the other way though, we start to see the problem as well (the float being the worst):
DECLARE @D1 DECIMAL(38,17) = 123456789012345.0
,@D2 DECIMAL(38,17) = 10.12345678
,@N1 NUMERIC(38,17) = 123456789012345.0
,@N2 NUMERIC(38,17) = 10.12345678
,@N3 NUMERIC(16,1) = 123456789012345.0
,@N4 NUMERIC(10,8) = 10.12345678
,@F1 FLOAT = 123456789012345.0
,@F2 FLOAT = 10.12345678
;
SELECT DecimalResult = @D1*@D2 --1249809467764053.493949 (not quite correct)
,NumericResult = @N1*@N2 --1249809467764053.493949 (not quite correct)
,FloatResult = @F1*@F2 --1.24980946776405E+15 = 1249809467764050 (very wrong)
,NumericResult = @N3*@N4; --1249809467764053.493949100 (Correct)
To repeat myself, here float is inappropriate. For what the OP is doing, float is also inaccurate as it is inappropriate; just like Jeff's choice of using a scale of 38 or store a number with a scale of 3.My mom always taught me to verify the correctness of my calculations with reverse operations.
So, let's see how accurate and trustful are your claims of correct and wrong calculations in the example above
DECLARE @D1 DECIMAL(38,17) = 123456789012345.0
,@D2 DECIMAL(38,17) = 10.12345678
,@N1 NUMERIC(38,17) = 123456789012345.0
,@N2 NUMERIC(38,17) = 10.12345678
,@N3 NUMERIC(16,1) = 123456789012345.0
,@N4 NUMERIC(10,8) = 10.12345678
,@F1 FLOAT = 123456789012345.0
,@F2 FLOAT = 10.12345678
;SELECT @D1 = @D1*@D2
,@N1 = @N1*@N2
,@F1 = @F1*@F2
,@N3 = @N3*@N4;SELECT [@D1] = @D1/@D2 --
,[@N1] = @N1/@N2 --
,[@F1] = @F1/@F2 --
,[@N3] = @N3/@N4; --
OK, straight away, numeric calculations with @N3 and @N4 are out:Msg 8115, Level 16, State 8, Line 15
Arithmetic overflow error converting numeric to data type numeric.
Why?
Because of this:SELECT SQL_VARIANT_PROPERTY ( @N3*@N4, 'precision') -- 27
, SQL_VARIANT_PROPERTY ( @N3*@N4, 'scale') -- 9
, SQL_VARIANT_PROPERTY ( @N3*@N4, 'TotalBytes'); -- 17
As you can see, the product is not of the same scale and precision as the original values defined by you.
Too keep the calculation accurate SQL Server has to add up precisions and scales of original decimals:
new p = p1 + p2 + 1
new s = s1 +s2
And, consequently, it double the amount of storage required for the value.But let's assume we have the gift of predicting which scales and precisions we're gonna need for our intermediate results and add another variable:
DECLARE @D1 DECIMAL(38,17) = 123456789012345.0
,@D2 DECIMAL(38,17) = 10.12345678
,@N1 NUMERIC(38,17) = 123456789012345.0
,@N2 NUMERIC(38,17) = 10.12345678
,@N3 NUMERIC(16,1) = 123456789012345.0
,@N4 NUMERIC(10,8) = 10.12345678
,@N5 NUMERIC(27,9)
,@F1 FLOAT = 123456789012345.0
,@F2 FLOAT = 10.12345678
;
SELECT @D1 = @D1*@D2
,@N1 = @N1*@N2
,@F1 = @F1*@F2
,@N5 = @N3*@N4;SELECT @D1 = @D1/@D2 --
,@N1 = @N1/@N2 --
,@F1 = @F1/@F2 --
,@N3 = @N5/@N4; --SELECT @D1 = @D1/@D2 --
,@N1 = @N1/@N2 --
,@F1 = @F1/@F2 --
,@N3 = @N5/@N4; --SELECT [@D1] = @D1 -- 123456789012344.99999900000000000 - a bit off
,[@N1] = @N1 -- 123456789012344.99999900000000000 - a bit off
,[@F1] = @F1 -- 123456789012345 - accurate
,[@N3] = @N3 -- 123456789012345.0 - accurateSo, big decimals clearly fail, float is 100% accurate, as well as smaller decimal.
But keep in mind - to keep up with float accuracy the"smaller" decimal had to use twice as much memory and 4 times as much CPU clocks.
Why "bigger" decimal could not keep up with the accuracy?
Well, to do so it would need to add up precisions and scales of original values, but it exceeded the maximum precision of 38.
So, it has to sacrifiy on scale to prevent aritmetic overflow.And now - the cherry on the cake.
It's becomes so clear how to break the "accuracy" of "appropriate" decimals.
Just add another operation, which will throw it over the precision limitations for DECIMAL data type:
DECLARE @D1 DECIMAL(38,17) = 123456789012345.0
,@D2 DECIMAL(38,17) = 10.12345678
,@N1 NUMERIC(38,17) = 123456789012345.0
,@N2 NUMERIC(38,17) = 10.12345678
,@N3 NUMERIC(16,1) = 123456789012345.0
,@N4 NUMERIC(10,8) = 10.12345678
,@N5 NUMERIC(14,8) = 1000.12345678
,@F1 FLOAT = 123456789012345.0
,@F2 FLOAT = 10.12345678
,@F3 FLOAT = 1000.12345678
;
SELECT DecimalResult = @D1*@D2*@D2/@D2/@D2 -- 123456789012344.999999
,NumericResult = @N1*@N2*@N2/@N2/@N2 -- 123456789012344.999999
,FloatResult = @F1*@F2*@F3/@F2/@F3 -- 123456789012345
,NumericResult = @N3*@N4*@N5/@N4/@N5; -- 123456789012344.999999
At the end - FLOAT wins in accuracy.
Float always wins in accuracy.
Here 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.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 24, 2018 at 6:43 am
Sergiy - Tuesday, July 24, 2018 6:35 AMAt the end - FLOAT wins in accuracy.
Float always wins in accuracy.
I'd suggest looking at the example I posted above, where Float fails to be more accurate even with basic addition.
The only certainty is that there is no one numeric data type that is always better than every other one (or there would be no alternatives). Also computers don't really do maths, they do a good approximation of it.
July 24, 2018 at 6:46 am
I not going to bother anymore. I was happy to debate this (I enjoy debating things like this, as several parties can learn), but this isn't one. You're only happy to argue (not debate) your side, and, (truth to told) started off it all with an insult towards me.
I've learned something here anyway, but still, I'm out.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 24, 2018 at 6:46 am
andycadley - Tuesday, July 24, 2018 5:57 AMSergiy - Tuesday, July 24, 2018 5:24 AMThom 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.Sergiy - Monday, July 23, 2018 7:37 PMSorry, that's totally wrong.
Here is the simple proof.
So simple you could not possibly miss it in primary school.Share a whole piece by 3 and then bring the 3 shares back together.
According to the math of any level it must be exactly the whole thing back in the end.
Let's see how it works with different data types:declare @DV decimal (12,10), @DN decimal (12,10)
declare @DV_big decimal (38,30), @DN_big decimal (38,30)
declare @FV FLOAT, @FN FLOATselect @DV = 1, @DN = 3
select @DV_big = 1, @DN_big = 3
select @FV = 1, @FN = 3select @DV = @DV/@DN
select @DV = @DV+@DV+@DVselect @DV_big = @DV_big/@DN_big
select @DV_big = @DV_big+@DV_big+@DV_bigselect @FV = @FV/@FN
select @FV = @FV+@FV+@FVselect @DV [Decimal 1], @DV_big [Decimal_big 1], @FV [Float 1]
Decimal 1 Decimal_big 1 Float 1
--------------------------------------- --------------------------------------- ----------------------
0.9999999999 0.999999000000000000000000000000 1
Impressive, ha?
So many useless zeros at the end of so inaccurate result.Feel free to choose any other precision and scale to make the decimal result look more accurate.
You can prove anything with edge cases:
select
cast(10000000 as float)+CAST(0.000000001 as float) As FloatResult,
cast(10000000 as decimal(38,10))+CAST(0.000000001 as decimal(38,10)) As DecimalResultgives
FloatResult DecimalResult
---------------------- ---------------------------------------
10000000 10000000.0000000010For which Float is clearly less correct than decimal.
As a datatype, float is exceptionally good for very small values OR very large values, but runs into all sorts of exciting issues if you mix and match the two. It does have a whole raft of special case logic for dealing with issues like 1/3 and is implemented in hardware and the processor level so is really, really fast. Decimal on the other hand is much better at dealing with a broader range of numbers but does require you to be more careful if you perform extensive maths on it because rounding errors near the limits of Precision/Scale can become cumulative without too much effort (as amply demonstrated).
Right tool for the right job, no silver bullets, no sacred cows...etc...
Your 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.
_____________
Code for TallyGenerator
July 24, 2018 at 6:53 am
sgmunson - 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.
_____________
Code for TallyGenerator
July 24, 2018 at 7:01 am
Thom A - Tuesday, July 24, 2018 5:36 AMThe specific point here, however, is that with the way the OP is doing their expression, float does result in the wrong answer, however, decimal (with an appropriate scale and precision) does. Eirikur, however, has expressed that if they use the correct tools (functions) for the job then that issue should be result. I've not had to deal with geographical data on SQL Server, so not something I can comment on though.
It's not floats which lead to the error.
As someone pointed out, all the functions used in the calculations take FLOATs as arguments and return FLOAT values.
It's adding up of integer constants with float results of intermediate calculations which lead to hidden conversions, which are at fault.
The formula just built inappropriately, with no understanding of float point calculations, therefore it causes issues.
_____________
Code for TallyGenerator
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply