Order of Operations in SQL Server: How PEMDAS Works in T-SQL

The order of operations in SQL Server is the one you learned in school, so 6/2*(1+2) returns 9. Parentheses go first, then multiplication and division, then addition and subtraction. Equal levels run from left to right.

What PEMDAS Says

PEMDAS stands for parentheses, exponents, multiplication, division, addition and subtraction. The same idea is called BODMAS in many countries. The letters make it look like six steps, but there are only four levels. Multiplication and division share one level. Addition and subtraction share another.

A tie inside one level is broken from left to right. That one rule decides 6/2*(1+2). The parentheses give 3, so the expression becomes 6/2*3. Division comes first because it sits on the left, which gives 3*3, and the answer is 9. Multiplying 2 by 3 first would give 6/6 and the answer 1, but that skips the left to right rule.

SQL Server follows the same order of operations. The query below runs the expression and four variations. The last one is a longer chain of mixed operators, which is where mistakes start.

SELECT 6/2*(1+2) AS Original,
       6/(2*(1+2)) AS DivideByWholeProduct,
       (6/2)*(1+2) AS ExplicitLeft,
       6/2*1+2 AS MixedLevels,
       3+8-4+5*9/3-8/2*1-2+5 AS LongChain;

In the long chain, 5*9/3 becomes 15 and 8/2*1 becomes 4. The rest is addition and subtraction from left to right, and the total is 21.

T-SQL Has No Implied Multiplication

The internet argument about 6/2(1+2) is about writing a number next to a parenthesis. Some people read that as multiplication with a higher priority. T-SQL avoids the whole debate, because it doesn’t accept the notation.

SELECT 6/2(1+2);

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1'.

In T-SQL, you write the operator you mean. If you want 6 divided by the whole product, write 6/(2*(1+2)). That returns 1, as the table above shows. A formula in a query has one reading, and the parentheses are how you state it.

The T-SQL Order of Operations in Full

The order of operations covers more than arithmetic. A query also mixes bitwise operators, comparisons and logic. The documented order runs from the strongest level to the weakest. Operators on one level are evaluated from left to right.

• Level 1: the bitwise NOT operator (~).
• Level 2: multiplication, division and modulo (*, / and %).
• Level 3: addition, subtraction, string concatenation and the bitwise operators &, ^ and |.
• Level 4: the comparison operators such as = and >.
• Level 5 to 7: NOT, then AND, then OR (with BETWEEN, IN and LIKE sharing the OR level).

Two surprises follow from that list. The bitwise operators sit on the addition level, not above it. In the next query, 1 | 2 * 3 multiplies first and returns 7. The expression 5 + 3 & 4 works from left to right and returns 0.

SELECT 1 | 2 * 3 AS BitOrAfterMultiply,
       (1 | 2) * 3 AS BitOrGrouped,
       5 + 3 & 4 AS PlusThenAnd,
       5 + (3 & 4) AS AndThenPlus;

The logic levels cause more damage in real queries. AND binds tighter than OR. In the first query below, the condition keeps every x row, plus the y rows where n is above 2. The demo uses a table variable with four rows. Nothing is left behind.

DECLARE @Item TABLE (n int, s varchar(5));
INSERT @Item VALUES (1,'x'), (2,'y'), (3,'x'), (4,'y');
SELECT n, s FROM @Item WHERE s = 'x' OR s = 'y' AND n > 2 ORDER BY n;
SELECT n, s FROM @Item WHERE (s = 'x' OR s = 'y') AND n > 2 ORDER BY n;

Three Traps With Numbers

Integer division. When both sides are integers, the result is an integer. 7/2*2 returns 6, because 7/2 becomes 3 first. 7*2/2 returns 7. Using a decimal such as 7/2.0*2 returns 7.000000. Likewise 10/4 returns 2, while 10/4.0 returns 2.500000. The order you write the operators in changes the answer. Adding a decimal anywhere changes the result type as well. Here 6/2*(1+2.0) returns 9.0, a numeric value, instead of the integer 9.

The negative sign. A unary minus is applied after a multiplication that follows it. So 100/-2*3 doesn’t mean (100/-2)*3. It means 100/-(2*3), and the result is -16, not -150. So 100/-2*3 has a different shape from 100/2*3: the minus sign pulls the 2*3 together first. Write 100/(-2)*3 and you get -150.

The caret. In T-SQL, ^ is a bitwise exclusive OR, not a power. 2^3 returns 1. Use POWER(2,3) for exponents. The sign matters here too: POWER(-2,2) returns 4, while -POWER(2,2) returns -4.

Do You Always Need Parentheses?

You could argue that parentheses everywhere make a query noisy. For one level of operators, that’s true, and 2+3+4 needs none. I add them whenever two levels meet, and always around a negative number after a division. Then nobody has to remember a rule.

What to Remember

The order of operations in T-SQL has no surprises once you know the four levels. Work from the inside out. Multiplication and division share a level, addition and subtraction share a level, and ties go left to right. T-SQL never guesses at a number next to a parenthesis, so write every operator.

When a result looks wrong, add parentheses to match the reading you expect and compare the two answers. Then keep the parenthesized version in the query. Each demo above uses only constants and a table variable, so there is nothing to clean up.

A formula is not a debate, it is a decision you write down with parentheses.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.