SQL Server Forums | SQLServerCentral.com / Article Discussions / Article Discussions by Author / Discuss content posted by Dave62 / Fun with real and float / Latest PostsInstantForum.NET v2.9.0SQL Server Forums | SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 25 Apr 2014 03:19:00 GMT20RE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspx[quote][b]sqlnaive (7/8/2013)[/b][hr]Pretty simple question but what a good discussion. Some great explanation by Hugo. :-)[/quote]+1Thu, 25 Jul 2013 09:15:43 GMTSQLRNNRRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspxPretty simple question but what a good discussion. Some great explanation by Hugo. :-)Mon, 08 Jul 2013 05:23:40 GMTsqlnaiveRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspxVery Nice ExplanationThu, 04 Jul 2013 12:23:06 GMTlouigopalRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspxNice one....Tue, 02 Jul 2013 05:19:11 GMTAnipaulRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspxNice question, thanks.Tue, 02 Jul 2013 00:46:06 GMTKoen VerbeeckRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspxThat's an easy one but worthwhile regarding the real and float data types. Thanks.Mon, 01 Jul 2013 22:55:26 GMTzymosRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspx[quote][b]L' Eomot Inversé (7/1/2013)[/b][hr][quote][b]Revenant (7/1/2013)[/b][hr][quote][b]L' Eomot Inversé (7/1/2013)[/b][hr]. . . The very earliest floating point hardware implementation (Zuse, 1941) was neither electronic nor part of a stored program computer and had much smaller precision (15 bits - so nominally decimal precision 3) and hence wasn't useful for any complex calculations unless a great lack of accuracy was acceptable.[/quote]Z1 was built in 1936.[url]http://en.wikipedia.org/wiki/Konrad_Zuse[/url][/quote]I should have said "commercial hardware implementation" instead of just "hardware implementation" . . .[/quote]Tom, as I said already several times, when you come to Redmond, or to the NW from Portland to Vancouver, we have to get together for drinks and I am buying.Mon, 01 Jul 2013 14:18:11 GMTRevenantRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspx[quote][b]Revenant (7/1/2013)[/b][hr][quote][b]L' Eomot Inversé (7/1/2013)[/b][hr]. . . The very earliest floating point hardware implementation (Zuse, 1941) was neither electronic nor part of a stored program computer and had much smaller precision (15 bits - so nominally decimal precision 3) and hence wasn't useful for any complex calculations unless a great lack of accuracy was acceptable.[/quote]Z1 was built in 1936.[url]http://en.wikipedia.org/wiki/Konrad_Zuse[/url][/quote]I should have said "commercial hardware implementation" instead of just "hardware implementation" because I was excluding Z1 (as something that didn't really work, so not an implementation) and Z2 (as something which was only a prototype for the Z3 (and a demonstration machine for the German military), not a properly engineered machine. I don't regard the Z1 as an implementation (because it didn't often work, or so I was told).Although construction of the Z1 started in 1936, it didn't finish until 1938 and even when finished the machining of its parts wasn't clean/accurate enough so the machine frequently went wrong (or at least so I was taught long ago). There was no working Z1 until Siemens worked with Zuse to build one that actually worked (rather than reconstructing one that worked sometimes) in the late 1980s (a valuable history project, I think).The Z2 (finished in 1941) was another one-off prototype, with no future - it sprawled over too a large space and was very much a string and sealing-wax piece of engineering - in effect it was the testbed in which some of the Z3s design was verified. The first working machine, which actually went into production, was the Z3 (also 1941), and that was the machine I meant.Although it wasn't what we today call an electronic stored program computer, it had a significant electrical component (mechanical relays) and did store program in its electromechanical store. Zuse had patented pretty well all of what is known as Von Neumann architecture a few years before Williams and Kilburn started developing a reliable non-mechanical store for a stored-program computer which in turn was a year or two before Von Neumann wrote his report. In fact floating point was probably the one area (apart, of course, from politics) where Von Neumann disagreed with Zuse - he was famously anti-floating-point.Mon, 01 Jul 2013 14:13:14 GMTTomThomsonRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspx[quote][b]L' Eomot Inversé (7/1/2013)[/b][hr]. . . The very earliest floating point hardware implementation (Zuse, 1941) was neither electronic nor part of a stored program computer and had much smaller precision (15 bits - so nominally decimal precision 3) and hence wasn't useful for any complex calculations unless a great lack of accuracy was acceptable.[/quote]Z1 was built in 1936.[url]http://en.wikipedia.org/wiki/Konrad_Zuse[/url]Mon, 01 Jul 2013 09:41:28 GMTRevenantRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspxYes, thanks to Hugo for the detailed explanation. I agree with those who say that the Microsoft documentation could use Hugo's addition!I think the following queries show the switchover point where the REAL number is returned as 100.[code]-- Num1 = 99.99999CREATE TABLE #QotD ( Num1 REAL, Num2 FLOAT );INSERT INTO #QotDVALUES ( 99.9999961, 99.99999999997 );SELECT *FROM #QotD;DROP TABLE #QotD;[/code][code]-- Num1 = 100CREATE TABLE #QotD ( Num1 REAL, Num2 FLOAT );INSERT INTO #QotDVALUES ( 99.9999962, 99.99999999997 );SELECT *FROM #QotD;DROP TABLE #QotD;[/code]Thanks again.- webrunnerMon, 01 Jul 2013 08:57:13 GMTwebrunnerRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspx[quote][b]Hugo Kornelis (7/1/2013)[/b][hr][quote][b](By the way, I think real should be considered to have maximum precision six; you should always calculate with at least one more digit then the precision to limit the effect of rounding errors - at least, that's what has been taught to me in high school).[/quote] Up that that point I thought your explanation was great, but here you are confusing the precision of representation with the accuracy or results. They aren't the same thing. Besides, your teachers were remiss in telling to use 1 rather than 2 or more - if you want final results accurate to N decimal places all starting values and intermediate results should be represented to a precision of at least N+2 decimal places unless the calculation to be carried out is utterly trivial. It's fine to work with N+1 places when the calculation is something rally very simple like a single multiplication, but if you are trying to compute the eigenvectors or the inverse of a 3 X 3 matrix (both calculations which are extremely common in elementary mechanics) working with N+1 places may give you horribly inaccurate results. 32bit floating point with precision 7 decimal places was intended to carry out complex calculations delivering results to a guaranteed 3 decimal places accuracy - an N+4 design rather than N+1 - and of course less complex calculations to greater guaranteed accuracy. The very earliest floating point hardware implementation (Zuse, 1941) was neither electronic nor part of a stored program computer and had much smaller precision (15 bits - so nominally decimal precision 3) and hence wasn't useful for any complex calculations unless a great lack of accuracy was acceptable.Mon, 01 Jul 2013 05:52:11 GMTTomThomsonRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspxPerfect Explanation by HugoMon, 01 Jul 2013 03:18:09 GMTsharath.chalamgariRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspxYes, very nice explanation. Thanks,IulianMon, 01 Jul 2013 02:25:42 GMTIulian -207023RE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspx[quote][b]Hugo Kornelis (7/1/2013)[/b][hr][quote][b]Mighty (6/30/2013)[/b][hr]Probably Hugo can explain us why the maximum precision is 7 for 4 byte storage. :-D[/quote]Floating point numbers are stored in "exponential notation" - that is the only way to be able to store both very small and very large values and still maintain precision...[/quote]Thanks Hugo, guess Microsoft can use copy and paste to add it to their documentation. ;-)Mon, 01 Jul 2013 01:42:25 GMTMightyRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspx[quote][b]Mighty (6/30/2013)[/b][hr]Probably Hugo can explain us why the maximum precision is 7 for 4 byte storage. :-D[/quote]Floating point numbers are stored in "exponential notation" - that is the only way to be able to store both very small and very large values and still maintain precision.Exponential notation, aka scientific notation, is easiest explained in decimal, so let's start there. If you need to do computations that involve the mass of the sun and the atomic weight of Helium, it is impractical to work with values like 1989100000000000000000000000000000 g, or 0.00000000000000000000000664648 g. So instead, we use 1.9891 x 10[sup]23[/sup] and 6.64648 x 10[sup]-24[/sup], also written as 1.9891E23 and 6.64648E-24. The number before the E (mantissa) and the number after the E (exponent) are stored separately. If you did this with decimal values and you had two positions (plus an optional +/- sign) for the exponent and four positions (plpus +/- sign) for the mantissa, then your range of representable values would be 1.000E-99 to 9.999E+99 and -1.000E-99 to -9.999E+99.Float works the same, but is two-based. Both the mantissa and the exponent are stored as a binary value, but the exponential calculation is also based on binary numbers. And I [b]assume[/b] it also lowers the mantissa to be between 0.5 and 0.999999, because that makes arithmetic operations easier. So internally, a value like for instance (decimal) 27 would be represented as 0.11011E+110 - the exponent (+110) is the binary representation of 6, so the actual value represented here is 0.11011(binary) x 2[sup]6[/sup](decimal) - or 11011 (binary), which is equal to 27 (decimal). (And the 0.11011 is actually stored as just 11011, so a mere five bits; the bit before the "decimal" separator is always zero and won't be stored).If you check Books Online ([url=http://msdn.microsoft.com/en-us/library/ms173773.aspx]http://msdn.microsoft.com/en-us/library/ms173773.aspx[/url]), you'll see that the values float can represent range between 1.18E - 38 and 3.40E + 38 (decimal based) (and obviously the same range for negative numbers). Compare this to the powers of two, and you'll see that 1.18E - 38 corresponds to 0.5 x 2[sup]-125[/sup] and 3.40E + 38 to 1 x 2[sup]128[/sup] (why mantissa 2 here? Because that is as close to binary 1.11111... as I can get). So obvously, the exponent is represented using eight bits, one for the sign, seven for the values 0 - 128. Two values are unused -maybe to handle under- and overflow? Or to enable future support for special cases like NaN or Inf? I don't know)Eight bits, or a single byte, for exponent, means three bytes left for the mantissa. That allows SQL Server to store 2[sup]24[/sup] or 16,777,216 different values. Enough to represent all possible decimal values with seven positions, not enough for all possible values with eight positions. Hence the maximum precision of seven.(By the way, I think real should be considered to have maximum precision six; you should always calculate with at least one more digit then the precision to limit the effect of rounding errors - at least, that's what has been taught to me in high school).Mon, 01 Jul 2013 01:25:37 GMTHugo KornelisRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspx[quote][b]Danny Ocean (6/30/2013)[/b][hr]Good question with real fun :-D[/quote]+1 :-PMon, 01 Jul 2013 00:24:14 GMTkapil_kkRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspxGood question with real fun :-DSun, 30 Jun 2013 23:38:56 GMTDanny OceanRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspxNice question! Thanks :-)Sun, 30 Jun 2013 21:13:24 GMTLokesh VijRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspxThe precision of a REAL is always 7. FLOAT(24) is a synonym for REAL. Don't understand why this is not explicitely mentioned in the MSDN article.Probably Hugo can explain us why the maximum precision is 7 for 4 byte storage. :-DSun, 30 Jun 2013 06:07:19 GMTMightyRE: Fun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspx[quote]There's probably a much deeper explanation than this, which I'm hoping will come to light in the discussion.[/quote]Not much depth needed. See [url=http://msdn.microsoft.com/en-us/library/ms173773.aspx]http://msdn.microsoft.com/en-us/library/ms173773.aspx[/url] and check the number of bytes used to represent real and float.Sun, 30 Jun 2013 04:38:49 GMTHugo KornelisFun with real and floathttp://www.sqlservercentral.com/Forums/Topic1468819-2739-1.aspxComments posted to this topic are about the item [B]<A HREF="/questions/float/99818/">Fun with real and float</A>[/B]Sat, 29 Jun 2013 12:54:30 GMTDave62