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 ««12345»»»

Exact and Approximate Expand / Collapse
Author
Message
Posted Tuesday, January 3, 2012 4:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 11,192, Visits: 11,096
No worries Hugo, though I am a little depressed that the values I chose don't make the difference easier to spot - it really wasn't my intention for this question to be a test of reading skills



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1229131
Posted Tuesday, January 3, 2012 4:07 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:39 AM
Points: 579, Visits: 27,690
Good question!
The following discussion is making it even better. We should be careful when using exact numeric data types both from the point of view of storing and calculations.

Thanks
Post #1229137
Posted Tuesday, January 3, 2012 5:34 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Good question Paul, thanks for submitting.

http://brittcluff.blogspot.com/
Post #1229173
Posted Tuesday, January 3, 2012 6:49 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 7:29 PM
Points: 8,562, Visits: 9,063
SQL Kiwi (1/3/2012)
No worries Hugo, though I am a little depressed that the values I chose don't make the difference easier to spot - it really wasn't my intention for this question to be a test of reading skills

It is a good question. Nicely shoots down the myth that these fixed point "exact" numerics have fewer (or less serious) rounding issues than floating point approximate numerics.

I would have thought that if anyone ran it they would notice the difference between 570 and 567 as the last three digits of the result, so it hardly seems a test of reading skills.

But why run it, when it's clear that using decimal(38,20) to multiply these two numbers will run you head on into the draconian rounding used for "exact" numerics?


Tom
Post #1229226
Posted Tuesday, January 3, 2012 7:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 18, 2012 8:54 PM
Points: 37, Visits: 29
Ohh come on!! I'm wrong again. I'm starting to think this is not meant for common people. LOL! But i'm learning much so thanks. ;)
Post #1229233
Posted Tuesday, January 3, 2012 7:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 11,192, Visits: 11,096
L' Eomot Inversé (1/3/2012)
It is a good question. Nicely shoots down the myth that these fixed point "exact" numerics have fewer (or less serious) rounding issues than floating point approximate numerics.

Thank you!

L' Eomot Inversé (1/3/2012)
I would have thought that if anyone ran it they would notice the difference between 570 and 567 as the last three digits of the result, so it hardly seems a test of reading skills.

That's perhaps true, but it did catch Hugo, so I do have a small regret about not using an example that left no room at all for misreading.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1229241
Posted Tuesday, January 3, 2012 7:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:56 PM
Points: 11,192, Visits: 11,096
Diogy (1/3/2012)
Ohh come on!! I'm wrong again. I'm starting to think this is not meant for common people. LOL! But i'm learning much so thanks. ;)

You're welcome!




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1229242
Posted Tuesday, January 3, 2012 7:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 4:28 AM
Points: 1,100, Visits: 4,898
Luckily I had had my coffee first!
Post #1229253
Posted Tuesday, January 3, 2012 7:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:22 PM
Points: 5,925, Visits: 8,174
L' Eomot Inversé (1/3/2012)
I would have thought that if anyone ran it they would notice the difference between 570 and 567 as the last three digits of the result, so it hardly seems a test of reading skills.

On my system, the results displayed ended in 57 (not 570) and 567 - both in grid and text mode.
But that's not what tricked me. I ran the code after replying. I had it wrong for the simple reason that I forgot to check the precision and scale of the result for the decimal, even though I am VERY much aware of this issue (and have, in fact, explained this on several online forums, inclding this one, a multitude of times already!).

If I had bothered to REALLY look at the resutls (instead of casually glancing them) after running the code, I would immediately have understood where my error was. But I didn't, I was already so convinced that Paul mistook the displayed result of the real value for the actual result that I neither checked the results with appropriate attention to detail, nor read the explanation accurately enough.
Sometimes, I have an idea stuck in my head so tightly that I fail to open my mind to other possibilities. Today was such a day.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1229277
Posted Tuesday, January 3, 2012 8:20 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 7:29 PM
Points: 8,562, Visits: 9,063
Hugo Kornelis (1/3/2012)
Sometimes, I have an idea stuck in my head so tightly that I fail to open my mind to other possibilities. Today was such a day.

It happens to all of us - and to me more often than I like to admit.


Tom
Post #1229304
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse