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

Operators Expand / Collapse
Author
Message
Posted Saturday, March 15, 2008 2:09 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments posted to this topic are about the item Operators
Post #469894
Posted Monday, March 17, 2008 3:02 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
the question could possibly be more interesting (at least to some readers) if the result of the division was not so obviously an integer, eg:

select 25 / 6 + 6 * 5


http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Post #470127
Posted Monday, March 17, 2008 7:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 2, 2008 6:35 AM
Points: 24, Visits: 38
This is why I don't always trust BOL to give me a straightforward and direct answer.

For example, under the topic "Operator Precedence (Transact-SQL)" it states "Operators have the precedence levels shown in the following table. An operator on higher levels is evaluated before an operator on a lower level."

My question with this then is "what defines a higher level" ?

The table then goes on to show * (Multiply), / (Division) on level 2 AND + (Add), - (Subtract) on level 3.

Since the levels are shown an ascending order, apparently level 2 is really meant to be a higher level than level 3 since it is physically higher in the list ??

The BOL documentation would have made more sense if the levels had been renumbered and shown in descending order so that the "higher" level is really the "higher" level both numerically and also by the physical location of the level within the table.

Here's what the current table looks like in BOL....


Level Operators

1 ~ (Bitwise NOT)

2 * (Multiply), / (Division), % (Modulo)

3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)

4 =, >, , !< (Comparison operators)

5 ^ (Bitwise Exclusive OR), | (Bitwise OR)

6 NOT

7 AND

8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME

9 = (Assignment)


I only got the QOD correct because I tried it myself first. But if I had relied on BOL, I would have gotten it wrong.




Post #470201
Posted Monday, March 17, 2008 7:34 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 6:55 AM
Points: 441, Visits: 934
Implied vs Explicit

I prefer to use parenthesis to clearly express the precedence. This saves me from having to mentally reestablish the precedence every time I read my own code. Also, a lesser experienced developer might go astray on this.

(25 /5) + (6 * 5) in my humble opinion, makes it clearer with little extra effort -- you DID know the evaluation order of the statement when you created it.

If you are using a more complicated formula and instead of hard-coded numbers, actual column names, the benefit is even more pronounced.

Also, the question of using 25 / 6 (instead of 25 /5) is interesting. In addition to all the precedence rules, you also have to establish the difference between
SELECT 25 / 6 [4]
SELECT 25.0 / 6, 25.0 / 6.0 [4.166666] in both cases.


I would also code in the appropriate ROUND and CEILING or FLOOR or CONVERT()
to make it clear how to deal with integer: decimals rounded-off or truncated.

Post #470227
Posted Monday, March 17, 2008 9:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:38 PM
Points: 31,355, Visits: 15,819
You should always use parenthesis to be sure that there isn't confusion. and to be sure if your server upgraded and the order changed, that you'd not have strange math occurring.

The table shows order of evaluation. So 1 is first, 2 second, etc., which is why 2 is higher than 3. I'm a native English speaker, so that's what I read, but I guess there could be confusion.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #470304
Posted Monday, March 17, 2008 4:04 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 20,857, Visits: 32,871
Since all that was involved was division, addition, and multiplication; I was able to identify the correct answer without testing or reading, the order of precedence for /,*.+,- is a known, and won't change. If the individual had thrown in bitwise operations and such, then I would have had to seriously thought about it, and perhaps done some testing/reading.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #470552
Posted Tuesday, March 18, 2008 6:50 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 3, 2014 6:55 AM
Points: 441, Visits: 934
In the trivial statement, I also got the answer right without testing or reading BOL.

However, even without bit operators, I have had cases where the statement was more complicated than this and column names were used, making the exercise more difficult. And I still think it is a form of documentation which, at worst can be ignored but can still be useful for next developer who maintains my code. And the question still remains, did the author of the test statement really intend to do strict integer divisions? Unless 100 % of the developers who tried this question also got it right on the first time, anything less than 100 % would support the fact even a trivial statement can be misunderstood.

Regards
Post #470760
Posted Tuesday, March 18, 2008 7:00 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 20,857, Visits: 32,871
I will agree.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #470766
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse