Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Fun with real and float Expand / Collapse
Author
Message
Posted Saturday, June 29, 2013 12:54 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:23 AM
Points: 2,005, Visits: 2,002
Comments posted to this topic are about the item Fun with real and float
Post #1468819
Posted Sunday, June 30, 2013 4:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:02 PM
Points: 5,916, Visits: 8,167
There's probably a much deeper explanation than this, which I'm hoping will come to light in the discussion.

Not much depth needed. See http://msdn.microsoft.com/en-us/library/ms173773.aspx and check the number of bytes used to represent real and float.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1468856
Posted Sunday, June 30, 2013 6:07 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:31 AM
Points: 3,194, Visits: 1,233
The 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.
Post #1468858
Posted Sunday, June 30, 2013 9:13 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 6:38 PM
Points: 1,371, Visits: 1,560
Nice question! Thanks

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1468915
Posted Sunday, June 30, 2013 11:38 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Good question with real fun



Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1468927
Posted Monday, July 1, 2013 12:24 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 1,888, Visits: 2,326
Danny Ocean (6/30/2013)
Good question with real fun

+1



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1468937
Posted Monday, July 1, 2013 1:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:02 PM
Points: 5,916, Visits: 8,167
Mighty (6/30/2013)
Probably Hugo can explain us why the maximum precision is 7 for 4 byte storage.

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 1023 and 6.64648 x 10-24, 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 assume 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 26(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 (http://msdn.microsoft.com/en-us/library/ms173773.aspx), 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-125 and 3.40E + 38 to 1 x 2128 (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 224 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).



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1468952
Posted Monday, July 1, 2013 1:42 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 4:31 AM
Points: 3,194, Visits: 1,233
Hugo Kornelis (7/1/2013)
Mighty (6/30/2013)
Probably Hugo can explain us why the maximum precision is 7 for 4 byte storage.

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...

Thanks Hugo,

guess Microsoft can use copy and paste to add it to their documentation.
Post #1468962
Posted Monday, July 1, 2013 2:25 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 1:33 AM
Points: 904, Visits: 837
Yes, very nice explanation.
Thanks,
Iulian
Post #1468971
Posted Monday, July 1, 2013 3:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:49 AM
Points: 1,179, Visits: 783
Perfect Explanation by Hugo
Post #1468978
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse