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 «««1234»»

Arithmetic 1 Expand / Collapse
Author
Message
Posted Wednesday, February 29, 2012 8:53 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 3:32 PM
Points: 18,068, Visits: 16,111
Thanks Ron



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1259628
Posted Wednesday, February 29, 2012 8:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 PM
Points: 2,717, Visits: 3,857
Good question, but I feel compelled to point out that all statements would fail on a Case Sensitive Collation because the variable @b is declared, but @B is referenced. Also in example #5 @A is declared and @a is referenced.

In my opinion as rare as it may be to be in a case sensitive database, one should always be consistent in coding.

**** I'm not trying to bust your chops, just making a point.


______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #1259630
Posted Wednesday, February 29, 2012 9:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 7:18 PM
Points: 7,930, Visits: 9,654
Nice question.

I think the reason for the error in the second batch is that in order to do implicit conversion for arguments of an operand the compiler must know what type is required (maybe this is what Hugo was trying to say); this is true for all operators, in particular for all arithmetic operators (+,-,*,/,%) with only the + case failing to produce an error when both arguments have string types, and that only because the '+' symbol is overloaded with sting concatenation as well as with the various numeric variants of addition (unrounded, or with various different kinds of rounding).
POWER of course is different: in T-SQL it isn't an operator (it's a function), so the same rules don't apply. The first argument is explicitly required to be float (ie float(53)) or something that can be explicitly converted to it, so there's no issue with implicit conversion for that. The second parameter breaks the rules that would apply if POWER were an operator, because in theory compiler doesn't know what it has to be converted to (it can have any exact or approximate numeric type except bit, or any type implicitly convertible to an exact or approximate numeric), but I suspect that in practise it is converted to float (it is probably documented somewhere, but I've no idea where) and if my suspicion is justified the specification could have been written the same way for the second parameter as for the first and we would then be able to have an operator-style syntax for it (** or ^ or ↑ as in many languages) while maintaining the implicit conversion rules, instead of having to use function syntax for this arithmetic operator.


Tom
Post #1259648
Posted Wednesday, February 29, 2012 10:21 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:20 AM
Points: 1,490, Visits: 1,060
Rock-solid question thanks to Ron and his staff of reviewers!
Post #1259714
Posted Wednesday, February 29, 2012 10:43 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: Today @ 12:48 AM
Points: 568, Visits: 76,803
Nice question that I think most of have run into at some point by accident. Thanks.
Post #1259727
Posted Wednesday, February 29, 2012 2:31 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, October 24, 2014 12:43 PM
Points: 4,126, Visits: 3,428
Very nice, Ron - thanks!
Post #1259816
Posted Wednesday, February 29, 2012 4:35 PM


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: 2 days ago @ 2:31 PM
Points: 3,129, Visits: 3,250
Hi All,
I tried to make a conclusion based on the data type precedence, but did not manage.
If you see the data type precedence
http://msdn.microsoft.com/en-us/library/ms190276.aspx
, it is also ambiguous for "%" showing different behavior against some cases for "*" and "/".
* (Multiply), / (Division), % (Modulo) belong to level 2, while the others are in lower levels and for which there are no errors.

In any case, interesting example!




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1259868
Posted Wednesday, February 29, 2012 5:29 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176, Visits: 778
tks for the question
Post #1259877
Posted Thursday, March 1, 2012 9:20 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: Tuesday, December 23, 2014 11:31 AM
Points: 3,422, Visits: 2,038
Nice question, thanks!
Post #1260248
Posted Thursday, March 1, 2012 9:46 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, November 20, 2014 5:13 AM
Points: 1,131, Visits: 1,394
Nice question. Thanks.

Thanks
Post #1260597
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse