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


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:16 PM
Points: 20,462, Visits: 14,091
Thanks Ron



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1259628
Posted Wednesday, February 29, 2012 8:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 4:13 PM
Points: 2,665, Visits: 3,630
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


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 8,287, Visits: 8,738
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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 1:38 PM
Points: 1,305, Visits: 1,054
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 @ 5:58 PM
Points: 565, Visits: 65,729
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: Today @ 4:18 PM
Points: 4,245, Visits: 3,324
Very nice, Ron - thanks!
Post #1259816
Posted Wednesday, February 29, 2012 4:35 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:45 AM
Points: 2,728, Visits: 2,645
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!
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 01, 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: Wednesday, April 16, 2014 9:46 AM
Points: 3,150, Visits: 1,900
Nice question, thanks!
Post #1260248
Posted Thursday, March 01, 2012 9:46 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:42 AM
Points: 890, Visits: 1,179
Nice question. Thanks.

Thanks
Post #1260597
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse