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

Question of the Day for 29 Jan 2007 Expand / Collapse
Author
Message
Posted Sunday, January 28, 2007 12:05 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments posted to this topic are about the Question of the Day for 29 Jan 2007 posted at http://www.sqlservercentral.com/testcenter/qod.asp?QuestionID=962.
Post #340345
Posted Monday, January 29, 2007 7:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 29, 2012 11:47 AM
Points: 21, Visits: 43

This was an interesting question.  Such a simple thing, of course it would be 3.33333... ad infinitum.  Not so, when I tested it!  When the answer appeared, I wondered whether the default data type was variant.  Then I wondered what would happen with 10.0 / 3 ... which also turned out to be an interesting result.

Thanks for keeping our brains agile.




Norm Johnson

"Keep smiling ... it gives your face something happy to do
        ... and it makes people wonder what you're up to!"
   
Post #340461
Posted Monday, January 29, 2007 1:05 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 24, 2007 8:14 AM
Points: 23, Visits: 1
OK, no testing....
What does this return?
select 10 / 3.3

And this?
select 10 / 3.33

You can see a nice (if you like math) progression happening there. But what if you're really bored, and you keep adding 3s? Well, if you go far enough, you get this:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type numeric.

Fair enough, it would be silly to expect SQL Server to put up with an infinite number of digits. But if you add those threes one-by-one, you'll see some interesting results.

select 10 / 3.33333333333333333
returns
3.0000000000000000030


select 10 / 3.333333333333333333
returns
3.000000000000000000

So presumably, at this point, we've run out of degrees of accuracy (can't remember the actual term for that, but y'all get the point). But wait, look at the length of the returned values--the new one hasn't just pushed the three out of the visible range, it's actually gotten shorter!

Let's add another three:
select 10 / 3.3333333333333333333
returns
3.00000000000000000

It's one character shorter again! And if we keep adding threes, we keep getting shorter and shorter results, all the way until

select 10 / 3.333333333333333333333333333333
returns
3.000000

And then, of course, we add one more three and are told that our arithmetic has overflowed.

Total usable value of this experiment: not really much at all. But it was fun.

Anyone know the intricacies of the math engine well enough to describe what's happening to produce the shorter results? My guess is that it's something quite simple, but I'd be interested to know what the machinations are.

JeremyNYC


Post #340613
Posted Thursday, November 22, 2012 11:59 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, December 14, 2014 11:09 PM
Points: 1,962, Visits: 2,406
this is not the the question of the standard of SQl Server central


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1388059
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse