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) -
This was removed by the editor as SPAM
-
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
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply