SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Power


Power

Author
Message
abinder-682132
abinder-682132
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 30
With all due respect, I failed to get my point accross. If I use power(2.0,31) I don't get an error. SQL Server should have been intelligent enough to anticipate my needs and use a float or bigint, etc. Software should be 'smart' not sarcastic. It knew the answer but insisted I change something to get it.
Alvin Ramard
Alvin Ramard
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4193 Visits: 11639
I'm sorry abinder but I think you're not considering a very important point.

It would not be appropriate for SQL to change the data type of the answer unless it was specifically told to do so. If you are just running a query in query analyzer where you just want to see the result then it's no big deal, but what if the results are to be passed to another process, or stored in a table, that cannot handle the "changed" data type?

You need to look at the full picture before saying that SQL Server should .....



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help
abinder-682132
abinder-682132
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 30
The int data type is the default data type for this statement. The default could just as well have been bigint. If I use, or even anticipate using, power(2,31) I obviously intended the statement to handle it and would have typed any resultant variable or table to handle it. I guess I'm mostly mystified by an error statement that returns an error AND the correct answer at the same time. It almost seems like a programmer was trying to have a little fun.

Anyway, I apprecaite your point of view. The long and short of it is whether I get an error at Power(2,31) or if it didn't give me the error and I used the result sometime later in an int datatype, I would have gotten an error either way.
Old_D
Old_D
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 86
I looked at it: 2 to the 31st power is
2147483648---ackkkk sorry wrong, didn't even think of datatype. I suppose I could have run it in the query analyser and got Arithmetic overflow error for type int, value = 2147483648.000000. But seems like cheating.
Bet I pay attension to datatype limits in the future
Anipaul
Anipaul
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7373 Visits: 1407
It is giving an error message with the answer. Smile



Shaun McGuile
Shaun McGuile
SSChasing Mays
SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)

Group: General Forum Members
Points: 655 Visits: 1060
Guys think deeper, that question is not "what is 2^31" but "why does this fail"

Its about data type limitations. The correct answer is not given in the error message

the floating point answer is i.e. 2.0^31 notice the .00000 on its end.

It fails because it overflows the default/assumed type of int.

THINK!THINKTHINK! then think again.

strive to understand before you strive to be understood!

Hiding under a desk from SSIS Implemenation Work Crazy
Anipaul
Anipaul
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7373 Visits: 1407
Yeah, I got your point. It is about data type limitation. The question is well written...



Aravind Tharesan
Aravind Tharesan
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 3
Just Cast the numeric expression value as float will give the value of power(2,31)

select POWER (CAST(2 AS FLOAT),31)
Melville
Melville
SSC Veteran
SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)SSC Veteran (261 reputation)

Group: General Forum Members
Points: 261 Visits: 18
It is a good question.

To work around we can cast as float or bigint; but the nub of the answer is in realising that it is the internal datatype of an undeclared 2 (ie an int) that is in question.

If Sql 2005 had all numbers internally as bigint it would cost memory, if it cast based on a guessed intention it would be slated to high heaven!

So an undeclared 2 is an int and a power operation on an int returns an int. Wishing that a power operation on an int would sometimes return an int and sometimes a bigint (what about a tinyint?) seems odd.

All seems reasonable to me.



kapil_kk
kapil_kk
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3105 Visits: 2766
answer results in an error message of Arithmetic over flow
+1 :-)

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
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