Float Data Type
-
Comments posted to this topic are about the item Float Data Type
-
Interesting question Mark!
With no idea about the question, I went ahead with a fluke and got that correct 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Lokesh Vij (6/6/2013)
Interesting question Mark!With no idea about the question, I went ahead with a fluke and got that correct 🙂
+1
Can anybody give correct reason for this result ?
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!! -
Lokesh Vij (6/6/2013)
Interesting question Mark!With no idea about the question, I went ahead with a fluke and got that correct 🙂
+1
waiting for experts views on this 😛
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
Interesting question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
This query shows the difference in the result.
SELECT I, N, N - 0.115
FROM @T
IN(No column name)
10.1150
20.115-1.38777878078145E-17
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.
-
Whitout running the code it is only a guess.
-
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)
-
Instead of this syntax:
SELECT I, N
FROM @T
WHERE N > 0.114
AND N < 0.116
I prefer this one:
SELECT I, N
FROM @T
WHERE ABS(N - 0.115) < 0.0000001
It's more accurate.
-
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.
_______________________________________________
www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore) -
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.
I wish that MS would move forwards and provide floating point to the latest standard, which includes the option of using the exponent field to indicate powers of 10 instead of powers of 2, reducing the representation issue to the same as for the numeric data types, and provides for a 128-bit format giving precision as good as 35 digit decimal types while retaining a vastly greater representation range of magnitudes.
And I also wish that people would learn the dangers of using a fixed point type like numeric(35,16) instead of using float(53). There are numerous cases where the latter is appropriate and the former will deliver nothing but arithmetic overflow errors, other cases where the float type works fine and delivers the required performance while the fixed point type produces a correct result provided one uses arcane programming techniques to ensure that the order of computation is such as to avoid overflow but uses vastly more storage runs so slowly that it is not useful. Then we might stop seeing silly comments like the above. There are cases where fixed point types work better than floating point - for example cases where business rules or financial regulations requires extremely frequent rounding - but these are by no means all cases.
Tom
-
Koen Verbeeck (6/6/2013)
Interesting question, thanks.agreed.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Interesting, really interesting. I admit I had to go with my gut feeling.
-
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)
People should be aware that you also have to remember that in binary these are recurring fractions, and that a lot more than 16 significant bits are stored in a float (53 in FLOAT, 24 in REAL, IIRC) and make sure that the initial substrings you are using are long enough to give the same result as the floating point hardware will get (remembering too that the hardware will use some extra bits to minimise rounding errors). I imagine you did check that you had enough bits, but most people reading what you wrote won't realise that it's necessary to do that.
Tom
-
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).
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply