Power

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Rakesh Kumar » Power

20 posts, Page 2 of 2

««1 2

Power

Rate Topic

Display Mode

Topic Options

Author

Message

abinder-682132

Posted Friday, August 01, 2008 11:46 AM

Grasshopper

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

Alvin Ramard

Posted Friday, August 01, 2008 11:53 AM

Hall of Fame

Group: General Forum Members
Last Login: Saturday, November 10, 2012 8:24 AM
Points: 3,031, Visits: 7,383

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

abinder-682132

Posted Friday, August 01, 2008 12:32 PM

Grasshopper

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

Old_D

Posted Friday, August 01, 2008 1:51 PM

Valued 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

Anipaul

Posted Monday, August 04, 2008 2:06 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 4,787, Visits: 1,335

It is giving an error message with the answer. :)

Post #545917

Shaun McGuile

Posted Monday, August 04, 2008 2:39 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 04, 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 Crazy

Post #545922

Anipaul

Posted Monday, August 04, 2008 3:01 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 4,787, Visits: 1,335

Yeah, I got your point. It is about data type limitation. The question is well written...

Post #545930

Aravind Tharesan

Posted Tuesday, August 05, 2008 11:39 PM

Forum Newbie

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

Melville

Posted Thursday, August 07, 2008 10:11 AM

SSC 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

kapil_kk

Posted Tuesday, December 11, 2012 2:55 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:16 PM
Points: 1,061, Visits: 1,151

answer results in an error message of Arithmetic over flow
+1 Smile

Post #1394975

« Prev Topic | Next Topic »

20 posts, Page 2 of 2

««1 2

Permissions