Negative (-) is an operator and will affect the order of operations.

An interesting question came to my attention the other day.

A strange operation problem in SQL Server: -100/-100*10 = 0

Basically the poster showed the following:

• If you execute SELECT -100/-100*10 the result is 0.
• If you execute SELECT (-100/-100)*10 the result is 10.
• If you execute SELECT -100/(-100*10) the result is 0.
• If you execute SELECT 100/100*10 the result is 10.

Interesting right? These are integers so there aren’t any decimal results so I’m going to switch them to decimals to make it easier for me to see what’s going on.

• If you execute SELECT -100.0/-100.0*10.0 the result is 0.1.
• If you execute SELECT (-100.0/-100.0)*10.0 the result is 10.
• If you execute SELECT -100.0/(-100.0*10.0) the result is 0.1.
• If you execute SELECT 100.0/100.0*10.0 the result is 10.

Still strange results but I much prefer the 0.1 instead of the 0. Next I’m going to group them a bit.

SELECT (-100.0/-100.0)*10.0;
SELECT 100.0/100.0*10.0;

The result of both of these is exactly what you would expect right? That’s because order of operations goes from left to right unless there are parentheses. In both cases the 100.0/100.0 and the -100.0/-100.0 are executed first with a result of 1.0 times 10.0 returns 10.0.

So how about this one

SELECT -100.0/(-100.0*10.0)

Still what you would expect. -100.0*10.0 runs first (it’s in parentheses) and returns -1000.0, then comes the -100.0/-1000.0 for the end result of 0.1.

Last but certainly not least our problem child.

SELECT -100.0/-100.0*10.0

In the absence of parenthesis and nothing but multiplication and division it should go left to right. Right? But that would give us -100.0/-100.0 returning 1.0 and finally 1.0*10.0 giving us 10.0. But that’s not what we are getting. Somehow we are ending up with 0.1. Well, unfortunately in SQL Server the – is considered an operator with lower precedence than multiplication and division. Which means that the code actually translates to

SELECT -(100.0/-(100.0*10.0))

Which could cause some really interesting calculation errors.

Fortunately most of the time we use variables and/or columns and end up with

DECLARE @var1 int = -100, @var2 int = -100, @var3 int = 10;
SELECT @var1 / @var2 * @var3;

And since the negative values are contained in the variables/columns it executes exactly the way you would expect. (Returns 10).

All of that said, if you are using T-SQL for a lot of math you really need to be aware of this and be careful in what you are doing.