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 ««12

SUM of FLOAT inconsistency Expand / Collapse
Author
Message
Posted Monday, October 13, 2008 11:12 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:47 AM
Points: 5,449, Visits: 1,401
Nice question......


Post #585202
Posted Tuesday, October 14, 2008 12:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:03 AM
Points: 2,599, Visits: 2,447
brewmanz (10/13/2008)
T


It always is correct WITH THIS SET OF DATA. Try adding a couple of zero(e)s at the end of each of the 3 numbers and watch "Msg 8115, Level 16, State 6, Line 12 - Arithmetic overflow error converting float to data type numeric." appear.
The FLOAT still works, and produces even stranger (but predictable) results of 1000164 and 1000192.

I prefer an overflow error instead of wrong data. You can specify a precision of 38 digits
From BOL:

Numeric data types that have fixed precision and scale.

decimal[ (p[ , s] )] and numeric[ (p[ , s] )]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.

p (precision)
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale)
The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

Post #585224
Posted Tuesday, October 14, 2008 6:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 24, 2011 7:50 AM
Points: 41, Visits: 83
brewmanz (10/13/2008)

Please don't tell my daughter. I take great delight in proofreading her work and finding mistakes (she works for a brochure publishing company). Sadly, it seems that I am capable of making mistakes, too. Soon I'll be into double figures this century

Ha ha, it happens to the best of us my friend. Seems like life enjoys dishing out the humble pie every now and then.

Thanks again for the article. It's good for developers to be aware of, and judging by the results of the quiz, there are quite a few out there who could use the enlightenment.
Post #585404
Posted Wednesday, October 15, 2008 3:31 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:27 AM
Points: 1,411, Visits: 818
Brewmanz... keep them coming. That was great, and exactly what we all need! Good explanation, too. I found that it was dependent upon order, but could not figure out why. A+++ QotD!

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

Post #586606
Posted Monday, August 9, 2010 8:06 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:49 AM
Points: 1,451, Visits: 1,855
You can validate this using Excel as well. You will find the same thing in Excel also (Refer attachment)

Excellent post!


Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul


  Post Attachments 
SUM of FLOAT inconsistency.xlsx (5 views, 8.99 KB)
Post #965975
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse