Database Programming: Operator Precedence In SQL Server

[UPDATED 9 May 2008; the information presented doesn't exactly answer Greg's question. There's an update here; and DTS is discussed here ]

I received an inquiry yesterday from Greg Husemeier, who I met when he came to Redmond for the SQL Ranger program. Greg asked a great question:

I hope you don’t mind me running a quick programming question regarding order of operators as predicates in SQL 2005. I noticed that you discuss similar topics in your blog. I was looking at a SQL 2005 upgrade “lessons learned” PPT recently and it stated the following regarding the query processor:

In SS2005, we have slightly changed the order of operators as predicates so,

(A <> 0 AND B / A > 1) will be different in SS2K vs SS2005

Recommendation: Use parenthesis to force the order you wish. Parenthesis always get executed first.

Do you know if this is documented anywhere? I am unable to find a description of the differences in behavior between the two versions other than in this presentation.

This question begat a research jag, which resulted in the finding that there has indeed been a subtle change in this functionality between SQL Server 2000 (scroll to the bottom of the page) and SQL Server 2005 (no scrolling required). Here's the operator precedence list for each platform; note the change in the handling of positive and negative numbers:

SQL Server 2000:

+ (Positive), - (Negative), ~ (Bitwise NOT)
* (Multiply), / (Division), % (Modulo)
+ (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
^ (Bitwise Exclusive OR), | (Bitwise OR)
NOT
AND
ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
= (Assignment)

SQL Server 2005:

~ (Bitwise NOT)
* (Multiply), / (Division), % (Modulo)
+ (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
^ (Bitwise Exclusive OR), | (Bitwise OR)
NOT
AND
ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
= (Assignment)

Thanks for the question, Greg!

-wp

Comments

• Anonymous
  January 01, 2003
  This post will (hopefully) close a thread which runs here and here . When we last addressed this issue,

• Anonymous
  January 01, 2003
  This morning's post on Operator Precedence was a little misleading, as Scott pointed out in his comment:

• Anonymous
  May 06, 2008
  If I am summarizing the changes correctly (based on your excerpt above), the only difference is when the unary + and - operators get evaluated:

•  In SQL 2000, they are evaluated first (before any other operators)
•  In SQL 2005, they are evaluated after modulo (%) and before addition (+) operators All other operators appear to evaluate in the same precendence order with respect to each other. The example expression highlighted in green above (A <> 0 AND B / A > 1) is said to evaluate differently in SQL 2000 and SQL 2005, but I don't see any ungrouped use of the unary + or - that would affect their evaluation. Did I miss something in this example? Scott R.

• Anonymous
  May 06, 2008
  Hi Ward, As far as I know there is no such thing as implicit order of operations for ANDed predicates -- only OR'd.  And if you do want to force the order, parens won't do it, as the QO is smart enough to know how to remove them; you need to use a CASE expression. Note that the only time I've ever seen this needed is when someone is trying to work around a bad EAV design and a string column that can contain all sorts of values is implicitly getting converted to a number in the wrong order, e.g.: SELECT  EntityId FROM MyEAV WHERE  AttributeName = 'Price'  AND AttributeValue = 36.25 Depending on which index the optimizer decides to use, this may fail as the query processor tries to convert the "Product Name" attribute values to NUMERIC...