7
SELECT -1 * 100 / 10
SELECT 100 * -1 / 10

differ in result. First returns -10, second 0. Obviously it's caused by the order.

But I can't find any information that divisions have higher weighting than multiplications.

Looking at http://technet.microsoft.com/en-gb/library/ms190276%28v=sql.105%29.aspx multiplication and division are on the same level and reading on it's written

When two operators in an expression have the same operator precedence level, they are evaluated left to right based on their position in the expression.

Regarding this, the second query should be evaluated like this: 100 * -1 -> result / 10, shouldn't it?

iamdave
  • 12,023
  • 3
  • 24
  • 53
Nico
  • 1,175
  • 15
  • 33
  • 2
    well, you've got a `minus` in your query, which is also an operator. Try `select 100 * (-1) / 10 and see the difference... – Raphaël Althaus Nov 28 '13 at 10:27
  • 1
    Possible duplicate of [A strange operation problem in SQL Server （-100/-100\*10 = 0）](https://stackoverflow.com/questions/54513450/a-strange-operation-problem-in-sql-server-100-10010-0) – phuclv Feb 04 '19 at 15:24

2 Answers2

1

This is either a "flaw" in the documentation or in SQL Server itself: According to your link the negative operator - has a lower operator precedence than multiplication and division, yet it has an effect on the order of the evaluation. You can see this because you are only using int values so that -1/10 results in 0. If you would use floating point arithmetics everything would be fine:

SELECT -1 * 100 / 10.0
SELECT 100 * -1 / 10.0

Or you could use variables to "hide" the negate:

DECLARE @a int
DECLARE @b int
DECLARE @c int

SELECT @a=-1, @b=100, @c=10

SELECT @a*@b/@c
SELECT @b*@a/@c

And as usual with complex arithmetics (complex means more than one operator) you can use parentheses to enforce a certain order of evaluation like @Mack mentioned in his answer.

Ocaso Protal
  • 19,362
  • 8
  • 76
  • 83
0

select 100 *-Cast(1 as float)/Cast(10 as float)

You need to cast the field as either decimal or float, or even numeric.

Aniruddha Das
  • 20,520
  • 23
  • 96
  • 132
Mariam Jamila
  • 1