Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Operators


Operators

Author
Message
Tao Klerks
Tao Klerks
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1393 Visits: 1249
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.
dave.baldwin
dave.baldwin
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
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. w00t
J-440512
J-440512
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 949
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.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36427 Visits: 18764
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
My Blog: www.voiceofthedba.com
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24295 Visits: 37999
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.

Cool

Cool
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)
J-440512
J-440512
SSC-Addicted
SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)SSC-Addicted (447 reputation)

Group: General Forum Members
Points: 447 Visits: 949
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24295 Visits: 37999
I will agree.

Cool
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search