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


Predict output for ROUND function


Predict output for ROUND function

Author
Message
Kari Suresh
Kari Suresh
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1168 Visits: 373
Comments posted to this topic are about the item Predict output for ROUND function

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
vk-kirov
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4366 Visits: 4408
If I change the variables to anything from float(25) to float(53), I get the wrong answer (75.42)

What's the relation between this value and the values from the question (165.755, 165.555)?!

If i use flat(24) or less, I get the correct answer.

Ok, let's use float(24). The result is: 165.76, 165.55. Is this result correct?

There's no way to get a correct answer without cheating (copy-paste-execute) or making a guess :-) Oh, except this one: you should know SQL Server internal binary representation of floats (which is undocumented and may vary from version to version)...
Bhavesh_Patel
Bhavesh_Patel
SSC Eights!
SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)SSC Eights! (915 reputation)

Group: General Forum Members
Points: 915 Visits: 297
Does the explanation for the question has any relation with the output of the code? w00t

I think this question has nothing to do with your SQL Server Knowledge.



Bhavesh Patel

http://bhaveshgpatel.wordpress.com/
Saurabh Dwivedy
Saurabh Dwivedy
Right there with Babe
Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)

Group: General Forum Members
Points: 742 Visits: 340
I realized that the poster had cut-paste from the link he has included in the answer. This could have been avoided by careful proof-reading; It might have been a case of oversight.

However I am not clear what exactly the question is testing here? How is it that the rounding behaves differently for two very similar numbers?

When I use float(24) for I get 165.76 in the first select ---> implying rounding behaves as expected. When I use float(25) I get 165.75 --> implying there is no rouding; rounding does not behave as expected.

In the second select statement - the situation is reversed: Now when I use float(24), I get the result - 165.55 implying no rounding. When I use float(25) - I get 165.56 - implying rounding happens as expected.

So when a double byte precision is used for 165.755 - no rounding happens; When double byte precision is used for 165.555 rounding does happen.

What is so drastically different between these two numbers that's causing the difference in behavior?

Does any one know?

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!
bc_
bc_
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1696 Visits: 7333
Seemingly random behavior with a random explanation. Could someone provide a valid explanation?

bc
KevinC.
KevinC.
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
Points: 885 Visits: 504
I don't have an explanation, but I did do some testing and learned that the same error occurs with .255, .355, .755, and .855, but does not occur for .055, .155, .455, .555, .655, and .955.

I don't know if it's truncating the float at some point or not, but this is good to know if you need to do calculations and rounding using floats. ;-)
Scott Coleman
Scott Coleman
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7484 Visits: 1534
The "rounding" differences are actually taking place when the numbers are stored as real and float approximate numbers. These are IEEE standard formats, they are in fact documented. You can look at the binary values with this query:
SELECT   F24, ROUND(F24, 2), CAST(F24 AS VARBINARY(8)),
F25, ROUND(F25, 2), CAST(F25 AS VARBINARY(8))
FROM (SELECT CAST(165.755 AS FLOAT(24)) AS F24, CAST(165.755 AS FLOAT(25)) AS F25) x



FLOAT(1) through FLOAT(24) is stored as 4-byte single-precision floating point, or REAL. FLOAT(25) through FLOAT(53) is stored as 8-byte double-precision floating point.

The 8-byte version is 0x4064B828F5C28F5C. 406 is the sign bit and exponent, 4B828F5C28F5C is the mantissa. (There are 53 mantissa bits but the leading bit is always 1 and not stored, so the next 52 bits are shown as 13 hex digits). The exact value would be 4B828F5C28F5C28F5C (with 28F5C repeating forever), but it has to stop and round at the 13th character. The 54th bit of the mantissa is 0, so the remainder is discarded and what is stored is something like 165.754999999999. This rounding occurs as the characters "165.755" are converted to FLOAT, the ROUND() function comes later.

The 4-byte version is 0x4325C148. The sign bit and exponent take 9 bits (43 plus part of the 2), so the mantissa looks different from above because it is shifted one bit. If you divide 4B828F5C28F5C (followed by 28F5C forever) by 2 (or convert to binary, tack a extra 0 on the front, and regroup the bits into hex digits) you get 25C147AD147AD (followed by 147AD forever). When this is rounded off after 24 bits, the remainder starts with a 1 bit so the final 7 is rounded up to 8. You end up with roughly 165.75501.

So the ROUND() function is not rounding the exact value 165.755 in either case. With single-precision it is rounding something slightly larger and with double precision it is rounding something slightly smaller.



bc_
bc_
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1696 Visits: 7333
Thanks Scott!

bc
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68725 Visits: 18570
Thanks Scott.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Dan Guzman - Not the MVP
Dan Guzman - Not the MVP
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1342 Visits: 751
Great explanation Scott, that's very detailed and instructive. Unfortunately it does not address the behavior found by KevinC. Why is not the behavior you describe consistent in a given range of numbers?

Dan
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