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 ««12

Power Expand / Collapse
Author
Message
Posted Friday, August 1, 2008 11:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2008 2:25 PM
Points: 10, 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.
Post #545350
Posted Friday, August 1, 2008 11:53 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:19 PM
Points: 3,101, Visits: 7,807
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.
Post #545356
Posted Friday, August 1, 2008 12:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2008 2:25 PM
Points: 10, 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.
Post #545398
Posted Friday, August 1, 2008 1:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 26, 2010 7:48 AM
Points: 59, 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
Post #545459
Posted Monday, August 4, 2008 2:06 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,299, Visits: 1,378
It is giving an error message with the answer. :)


Post #545917
Posted Monday, August 4, 2008 2:39 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: Thursday, October 4, 2012 9:20 AM
Points: 583, Visits: 1,060
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
Post #545922
Posted Monday, August 4, 2008 3:01 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,299, Visits: 1,378
Yeah, I got your point. It is about data type limitation. The question is well written...


Post #545930
Posted Tuesday, August 5, 2008 11:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 8, 2011 7:08 AM
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)

Post #547272
Posted Thursday, August 7, 2008 10:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, January 22, 2009 9:09 AM
Points: 211, 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.



Post #548444
Posted Tuesday, December 11, 2012 2:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
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/
Post #1394975
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse