Precedence of operator in SQL

Question

Post reply

Precedence of operator in SQL

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
August 10, 2012 at 1:00 am

#280067

Hi all,
I have a update statement which is as A = B + C * D.
i would like to know which operations would be perform first wheather it would be B+C or C*D ..etc

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

Should follow BODMAS, i.e. Boolean, Division, Multiplication, Addition, Subtraction.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

SELECT(2 + 3) * 4;

SELECT 2 + (3 * 4);

SELECT 2 + 3 * 4;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Dwain Camps SSC Guru Points: 86978 More actions · Answer 3

derek.colley (8/10/2012)
Should follow BODMAS, i.e. Boolean, Division, Multiplication, Addition, Subtraction.

BOL has division/multiplication at level 2 while addition/subtraction are at level 3.

http://msdn.microsoft.com/en-us/library/ms190276.aspx

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

dwain.c (8/10/2012)
derek.colley (8/10/2012)
Should follow BODMAS, i.e. Boolean, Division, Multiplication, Addition, Subtraction.
BOL has division/multiplication at level 2 while addition/subtraction are at level 3.
http://msdn.microsoft.com/en-us/library/ms190276.aspx

So the BOL article shows that SQL Server confirms to BODMAS.

BODMAS stands for Brackets, Orders, Division, Multiplication, Addition, Subtraction.

(Orders = squared, cubed, square root etc.)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Dwain Camps SSC Guru Points: 86978 More actions · Answer 5

Are you trying to say that Division has precedence over Multiplication and Addition over Subtraction?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Phil Parkin SSC Guru Points: 246985 More actions · Answer 6

dwain.c (8/10/2012)
Are you trying to say that Division has precedence over Multiplication and Addition over Subtraction?

These are at the same level - and so are evaluated in the order in which they appear (I think). Try this:

select 5 * 6 / 4

select 6 / 4 * 5

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Dwain Camps SSC Guru Points: 86978 More actions · Answer 7

Ah... So you weren't trying to say that, which is what I suspected. 🙂

There's always been a controversy over that acronym: http://www.allvoices.com/contributed-news/6036567-bodmas-is-used-my-millions-at-school-is-it-correct

Quoting that article:

Now if one uses BODMAS on 5 – 2 + 3 we might think that A (addition) comes before S (subtraction) and we might therefore say that 5 – 2 + 3 is 5 – 5 (we did the addition of 2+3)
This is NOT correct. In fact 5 – 2 + 3 is equal to 3 + 3 (we correctly did S before A)
In fact 5 – 2 + 3 = 3 + 3 = 6

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Phil Parkin SSC Guru Points: 246985 More actions · Answer 8

dwain.c (8/10/2012)
Ah... So you weren't trying to say that, which is what I suspected. 🙂
There's always been a controversy over that acronym: http://www.allvoices.com/contributed-news/6036567-bodmas-is-used-my-millions-at-school-is-it-correct
Quoting that article:
Now if one uses BODMAS on 5 – 2 + 3 we might think that A (addition) comes before S (subtraction) and we might therefore say that 5 – 2 + 3 is 5 – 5 (we did the addition of 2+3)
This is NOT correct. In fact 5 – 2 + 3 is equal to 3 + 3 (we correctly did S before A)
In fact 5 – 2 + 3 = 3 + 3 = 6

I've never seen it put so clearly - nice one, thanks.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

SQL does exactly what should be done in mathematics:

5 - 2 + 3 = 5 + (-2) + 3

Now you can see that it's absolutely doesn't mater what you add first 🙂

If go down to machine code, in most of modern processors, there is no such thing as subtraction... Negative numbers are inverted and complement is added, then numbers are summed with discarding of carried digit:

5 - 2 =

101

-010 -> negative number is inverted and complement added: 101 + 1 = 110

=

101

+110

=[1]011

then carried digit (in []) is discarded and you get

= 011 which is 3

:w00t:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Phil Parkin SSC Guru Points: 246985 More actions · Answer 10

Eugene Elutin (8/10/2012)
SQL does exactly what should be done in mathematics:
5 - 2 + 3 = 5 + (-2) + 3
Now you can see that it's absolutely doesn't mater what you add first 🙂
If go down to machine code, in most of modern processors, there is no such thing as subtraction... Negative numbers are inverted and complement is added, then numbers are summed with discarding of carried digit:
5 - 2 =
101
-010 -> negative number is inverted and complement added: 101 + 1 = 110
=
101
+110
=[1]011
then carried digit (in []) is discarded and you get
= 011 which is 3
:w00t:

Wow, I'm glad I don't have to debug your code:-D

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Dwain Camps SSC Guru Points: 86978 More actions · Answer 11

Phil Parkin (8/10/2012)
Eugene Elutin (8/10/2012)
SQL does exactly what should be done in mathematics:
5 - 2 + 3 = 5 + (-2) + 3
Now you can see that it's absolutely doesn't mater what you add first 🙂
If go down to machine code, in most of modern processors, there is no such thing as subtraction... Negative numbers are inverted and complement is added, then numbers are summed with discarding of carried digit:
5 - 2 =
101
-010 -> negative number is inverted and complement added: 101 + 1 = 110
=
101
+110
=[1]011
then carried digit (in []) is discarded and you get
= 011 which is 3
:w00t:
Wow, I'm glad I don't have to debug your code:-D

+1 Phil!

Although Eugene is 100% correct.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 12

...
Wow, I'm glad I don't have to debug your code:-D

Yeah,

you better not

debug my code

without note

it can explode!

:hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]