Probably Hugo can explain us why the maximum precision is 7 for 4 byte storage. :-D
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/Data Platform MVP (2006-2016)
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis