June 8, 2013 at 3:34 am
Danny Ocean (6/6/2013)
Can anybody give correct reason for this result ?
When working with decimal (base 10) fractions, most people easily understand this. If you use a fixed number of decimals after the decimal dot, nobody is surprised that (1/3) + (1/3) + (1/3) is not equal to (1 + 1 + 1) / 3. That's because 1/3 needs an infinite amount of decimal places to be represented exactly, using a fixed maximum precision means you have to round at some point, and rounding errors add up. Simply put: 1/3 = 0.333, 0.33 + 0.333 + 0.333 is 0.999. We lost 0.00033333..... when rounding the division result, and didn't "automaGically" get that back when adding three copies of that number.
Every notation has these problems, just not for the same numbers. If you were to use a base-3 notation, you would be able to represent 1/3 and 2/3 exactly, with only one decimal place. So if you would build a computer that uses base-3 numeric representation, you would never get any of these rounding effects for these specific numbers. But you would get them with others.
Questions like the current one pop up every now and then in the QotD. They always build upon the confusion caused by numbers that do have an exact representation in decimal (base 10) notation, as used by us humans, as well as in the decimal/numeric data type; but do not have an exect representation in the binary (base 2) representation. Nobody is ever surprised to see rounding errors when using 1/3 or 1/7 (because the notation we are used to suffers rounding errors as well), but many are surprised to see rounding errors when using 1/5 or 115/1000 (because those do not have rounding errors in our internal notation, but do have them in binary representation).
June 8, 2013 at 4:28 am
nenad-zivkovic (6/7/2013)
From BOL http://msdn.microsoft.com/en-us/library/ms187912(SQL.100).aspx
Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators.
Should be "Avoid using float or real columns." Period. Exclamation mark.
This is just one of the examples how it can give you unexpected results.
Nonsense. These data types are called "approximate numeric data types" for a reason. They should be used for applications that work with approximate numeric data.
Prime example: scientific applications. They should use these data types for two reasons:
1. The scale of the data. Consider calculating the mass of a subatomic particle by multiplying its force by the square of its speed. The speed needs nine places before the decimal point; the force needs nine places behind the decimal point, and the mass will need at least 24 digits behind the decimal dot, otherwise it'll simply be rounded down to zero. Fixed precision numeric data simply is not designed to handle that, and you'll get serious rounding errors. Floating point data easily copes with these, and bigger challenges.
2. Scientific data usually come from measurements, which are approximate by default. When in high school, my science teacher drilled us to always read the instrucments to one digit more than their (or our!) precision, do our calculations with at least two digits more (to prevent rounding issues), but present the end result with no more digits than the lowest measure precision.
The problem with real/float data in SQL Server is that it uses the full (internal) precision for comparisons. Even down to fractions that are displayed.
June 8, 2013 at 4:43 am
Hugo Kornelis (6/8/2013)
nenad-zivkovic (6/7/2013)
From BOL http://msdn.microsoft.com/en-us/library/ms187912(SQL.100).aspx
Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators.
Should be "Avoid using float or real columns." Period. Exclamation mark.
This is just one of the examples how it can give you unexpected results.
Nonsense. These data types are called "approximate numeric data types" for a reason. They should be used for applications that work with approximate numeric data.
Prime example: scientific applications. They should use these data types for two reasons:
1. The scale of the data. Consider calculating the mass of a subatomic particle by multiplying its force by the square of its speed. The speed needs nine places before the decimal point; the force needs nine places behind the decimal point, and the mass will need at least 24 digits behind the decimal dot, otherwise it'll simply be rounded down to zero. Fixed precision numeric data simply is not designed to handle that, and you'll get serious rounding errors. Floating point data easily copes with these, and bigger challenges.
2. Scientific data usually come from measurements, which are approximate by default. When in high school, my science teacher drilled us to always read the instrucments to one digit more than their (or our!) precision, do our calculations with at least two digits more (to prevent rounding issues), but present the end result with no more digits than the lowest measure precision.
The problem with real/float data in SQL Server is that it uses the full (internal) precision for comparisons. Even down to fractions that are displayed.
+1
Data types always should be appropriate to the application use.
So, if you need integer use INT, if you need decimal use DECIMAL and so on.
June 8, 2013 at 3:47 pm
I think the deal with how MSFT handles floating point should be correct one.
DECLARE @a FLOAT(20)
DECLARE @b-2 FLOAT(53)
DECLARE @C FLOAT(53)
DECLARE @T TABLE(I INT IDENTITY(1,1), N FLOAT(53))
SET @a = 0.115
SET @b-2 = 0.075
SET @C = 0.04
INSERT @T
SELECT @a
INSERT @T
SELECT I, N
FROM @T
SELECT I, N
FROM @T
WHERE N = 0.115
SELECT I, N
FROM @T
WHERE N > 0.114
AND N < 0.116
Now the first select returns nothing
June 9, 2013 at 11:19 pm
Good one.....
June 10, 2013 at 7:06 am
Hugo Kornelis (6/8/2013)
1. The scale of the data. Consider calculating the mass of a subatomic particle by multiplying its force by the square of its speed.
2. Scientific data usually come from measurements, which are approximate by default. When in high school, my science teacher drilled us to always read the instrucments to one digit more than their (or our!) precision,
Thanks for beating me to it... float and real have their uses... the fun times are when you find financial applications that use them, and have to explain to accountants (and sometimes your boss who doesn't have a CS or Math Degree) what's going on.
Those are the fun times. 🙂
June 10, 2013 at 9:51 am
L' Eomot Inversé (6/7/2013)
silly comments like the above.
Hugo Kornelis (6/8/2013)
Nonsense.
Since I received such a harsh words from two respectable members of SSC, I feel like adding a few explanations to my statement 😉
Off-course, I would agree that there are places where floating point types could and should be used. You have named a few, like scientific calculations etc, I could probably name a few more. Those data types exist for a reason for sure. However I feel that those usages are far from 'numerous`, in fact I would call them 'very rare'.
I believe that most of us here, and a vast majority of DB developers everywhere are not working with scientific data but rather on some sort of corporate/business/industrial/financial and similar databases - and in the world of business data - having approximate values is never acceptable. Me personally have been working in banking/insurance industry most of my career where seeing float have always been a red flag issue. I can't remember ever having a requirement that needed floating points to be solved. On the other hand there have been a numerous times when problem was caused by using floats where it's not the place for it.
Fact is, people are using float types a lot without knowing it's behavior and possible issues. You won't believe (or maybe you would) how many times have I encountered even the monetary columns to be float. So my statement was not so much against float/real datatypes, but against using it carelessly.
So, maybe with amendment of "unless you are aware of their behavior and are sure to know what you are doing", I am still going to repeat "Avoid using float and real datatypes"
Best Regards
-Nenad
_______________________________________________
www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)
June 10, 2013 at 10:31 am
mtassin (6/10/2013)
the fun times are when you find financial applications that use them
nothing wrong with that, so long as you Round as necessary
June 10, 2013 at 10:27 pm
i cant understand that how the result will come,both are same that giving same output
can please tell me the how it is possible
Regards
Srinivasp
June 11, 2013 at 12:39 am
Srinivas.Pendyala (6/10/2013)
i cant understand that how the result will come,both are same that giving same outputcan please tell me the how it is possible
Hi Srinivas,
Sure! There's an excellent discussion about this issue, along with some explanations, in this forum discussion. Please read that first. If you have any questions after that, share them and we'll be happy to reply.
June 20, 2013 at 3:40 am
Nice one. 🙂
June 26, 2013 at 10:01 pm
martin.whitton (6/7/2013)
palotaiarpad (6/7/2013)
Whitout running the code it is only a guess.Not strictly true.
"Float" uses binary representations of numbers. Therefore, in this example:
0.115 translates to 0.000111010111000 in binary
0.075 translates to 0.000100110011001
0.04 translates to 0.000010100011110
Therefore 0.075+0.04 translates to 0.000100110011001+0.000010100011110 which equals 0.000111010110111 which does NOT equal 0.000111010111000
Or, in other words 0.115 converted to binary does NOT equal 0.075 plus 0.04 converted to binary.
If you know this you can answer the question without guessing!
(I used http://www.exploringbinary.com/binary-converter/ to do the binary conversions and http://www.exploringbinary.com/binary-calculator/ to confirm my binary calculation)
It works well when length is 24 or less instead of 53..
Any idea???????
DECLARE @a FLOAT(24)
DECLARE @b-2 FLOAT(24)
DECLARE @C FLOAT(24)
DECLARE @T TABLE(I INT IDENTITY(1,1), N FLOAT(24))
SET @a = 0.115
SET @b-2 = 0.075
SET @C = 0.04
INSERT @T
SELECT @a
INSERT @T
SELECT I, N
FROM @T
SELECT I, N
FROM @T
WHERE N = 0.115
SELECT I, N
FROM @T
WHERE N > 0.114
AND N < 0.116
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
June 27, 2013 at 1:35 am
psingla (6/26/2013)
It works well when length is 24 or less instead of 53..Any idea???????
1. The "or less" is irrelevant. SQL Server only uses two types of float internally - 4 bytes (equivalent to real) for length up to 24, and 8 bytes for length 25 and higher.
2. With numbers that cannnot be exactly represented within the available position, the exact rounding error depends on the exact amount of positions. I didn't do the math with the numbers you used (but you can - the methods to use and the websites that will help you are included in the message you quoted back). But it is very probably that with the 4-byte float calculation, one of the numbers is rounded up and the other is rounded down, whereas in the 8-byte situation, they are both rounded up or both rounded down.
June 27, 2013 at 1:47 am
psingla (6/26/2013)
martin.whitton (6/7/2013)
palotaiarpad (6/7/2013)
Whitout running the code it is only a guess.Not strictly true.
"Float" uses binary representations of numbers. Therefore, in this example:
......
It works well when length is 24 or less instead of 53..
Any idea???????
Hugo has posted just as I was compiling my reply, but I think it's still worth posting the following clarification:
As Tom pointed out earlier in this discussion, the number of significant bits has to be taken into account when making the calculation.
If you look at http://msdn.microsoft.com/en-us/library/ms173773%28v=sql.110%29.aspx you will see that SQL server uses either 24 bits or 53 bits for float values (with 53 being the default).
If you specify float(24), then 24 bits will be used. If you redo my calculation using 24 bits instead of 53 you will find that 0.075 converted to binary plus 0.04 converted to binary does equal 0.115 converted to binary (I have done the calculation to confirm this).
June 27, 2013 at 3:21 am
Thanks Martin and Hugo
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply