

SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 6:51 AM
Points: 2,145,
Visits: 2,149





SSCertifiable
Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086,
Visits: 8,354





Hall of Fame
Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:01 AM
Points: 3,329,
Visits: 1,323


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.




Ten Centuries
Group: General Forum Members
Last Login: Thursday, October 16, 2014 10:02 AM
Points: 1,372,
Visits: 1,566





Ten 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




SSCommitted
Group: General Forum Members
Last Login: Today @ 6:37 AM
Points: 1,938,
Visits: 2,374





SSCertifiable
Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086,
Visits: 8,354


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 10^{23} and 6.64648 x 10^{24}, also written as 1.9891E23 and 6.64648E24. 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.000E99 to 9.999E+99 and 1.000E99 to 9.999E+99.
Float works the same, but is twobased. 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 2^{6}(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/enus/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 2^{128} (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^{24} 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




Hall of Fame
Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:01 AM
Points: 3,329,
Visits: 1,323


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.




Ten Centuries
Group: General Forum Members
Last Login: Thursday, November 13, 2014 3:36 AM
Points: 1,012,
Visits: 1,000


Yes, very nice explanation. Thanks, Iulian




Ten Centuries
Group: General Forum Members
Last Login: Tuesday, November 18, 2014 10:02 AM
Points: 1,194,
Visits: 789


Perfect Explanation by Hugo



