SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fun with real and float


Fun with real and float

Author
Message
Dave62
Dave62
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3585 Visits: 2837
Comments posted to this topic are about the item Fun with real and float
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19257 Visits: 12426
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
Mighty
Mighty
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5017 Visits: 1752
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. :-D
Lokesh Vij
Lokesh Vij
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3396 Visits: 1599
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


Danny Ocean
Danny Ocean
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2256 Visits: 1549
Good question with real fun :-D

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com
kapil_kk
kapil_kk
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5510 Visits: 2768
Danny Ocean (6/30/2013)
Good question with real fun :-D

+1 :-P

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19257 Visits: 12426
Mighty (6/30/2013)
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Mighty
Mighty
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5017 Visits: 1752
Hugo Kornelis (7/1/2013)
Mighty (6/30/2013)
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...

Thanks Hugo,

guess Microsoft can use copy and paste to add it to their documentation. ;-)
Iulian -207023
Iulian -207023
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2533 Visits: 1248
Yes, very nice explanation.
Thanks,
Iulian
sharath.chalamgari
sharath.chalamgari
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2032 Visits: 798
Perfect Explanation by Hugo
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search