Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question of the Day for 29 Jan 2007


Question of the Day for 29 Jan 2007

Author
Message
Site Owners
Site Owners
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10093 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.
N.Johnson
N.Johnson
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
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!"

jeremy nyc
jeremy nyc
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
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
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
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/
Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8684 Visits: 885
A simple integer divide.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search