Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 SUM of FLOAT inconsistency Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, October 13, 2008 11:12 PM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, April 20, 2016 3:23 AM Points: 6,049, Visits: 1,407
 Nice question......
Post #585202
 Posted Tuesday, October 14, 2008 12:38 AM
 Hall of Fame Group: General Forum Members Last Login: 2 days ago @ 2:28 AM Points: 3,369, Visits: 3,191
 brewmanz (10/13/2008)TIt 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 digitsFrom 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 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
 SSCommitted Group: General Forum Members Last Login: Thursday, December 1, 2016 3:20 PM Points: 1,700, Visits: 918
 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 CarrierMCITP - Database Administrator (SQL 2008)MCSE: Data Platform (SQL 2012)
Post #586606
 Posted Monday, August 9, 2010 8:06 AM
 SSCommitted Group: General Forum Members Last Login: Yesterday @ 3:16 AM Points: 1,710, Visits: 2,117
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.