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