Database Programming: Operator Precedence In SQL Server

re: Database Programming: Operator Precedence In SQL Server

Scott R. — Tue, 06 May 2008 20:32:40 GMT

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.

re: Database Programming: Operator Precedence In SQL Server

Adam Machanic — Tue, 06 May 2008 21:31:16 GMT

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...

Database Programming: Operator Precedence In SSIS

Ward Pond's SQL Server blog — Tue, 06 May 2008 22:11:29 GMT

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

Database Programming: Operator Precedence in DTS is a Matter of Inheritance

Ward Pond's SQL Server blog — Sat, 10 May 2008 02:32:24 GMT

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