Arithmetic 1

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 bitbucket » Arithmetic 1

34 posts, Page 3 of 4

«««1 2 3 4 »»

Arithmetic 1

Rate Topic

Display Mode

Topic Options

Author

Message

SQLRNNR

Posted Wednesday, February 29, 2012 8:53 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332

Thanks Ron

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

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

Jason Selburg

Posted Wednesday, February 29, 2012 8:56 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:55 AM
Points: 2,582, Visits: 3,552

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. BigGrin

______________________________________________________________________

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

L' Eomot Inversé

Posted Wednesday, February 29, 2012 9:25 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:48 PM
Points: 7,088, Visits: 7,143

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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #1259648

Rose Bud

Posted Wednesday, February 29, 2012 10:21 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:42 AM
Points: 1,072, Visits: 1,026

Rock-solid question thanks to Ron and his staff of reviewers!

Post #1259714

DBA_Dom

Posted Wednesday, February 29, 2012 10:43 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 563, Visits: 58,976

Nice question that I think most of have run into at some point by accident. Thanks.

Post #1259727

Revenant

Posted Wednesday, February 29, 2012 2:31 PM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:31 AM
Points: 3,454, Visits: 2,529

Very nice, Ron - thanks!

Post #1259816

IgorMi

Posted Wednesday, February 29, 2012 4:35 PM

SSCommitted

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 1,878, Visits: 1,449

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