Question of the Day for 29 Jan 2007

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Testing Center » Question of the Day (QOD) » Question of the Day for 29 Jan 2007

Question of the Day for 29 Jan 2007

Rate Topic

Display Mode

Topic Options

Author

Message

Site Owners

Posted Sunday, January 28, 2007 12:05 PM

SSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,040, 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

N.Johnson

Posted Monday, January 29, 2007 7:35 AM

Grasshopper

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

jeremy nyc

Posted Monday, January 29, 2007 1:05 PM

Grasshopper

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

kapil_kk

Posted Thursday, November 22, 2012 11:59 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:40 PM
Points: 1,059, Visits: 1,150

this is not the the question of the standard of SQl Server central

Post #1388059

« Prev Topic | Next Topic »

Permissions