Why does order of T-SQL where statements matter?

Question

Why, when opening a query like the following:

WHERE (statement1) AND ((statement2) OR (statement3))

does SSMS query designer refactor it into the following syntax:

WHERE (statement1) AND (statement2) OR (statement1) AND (statement3)

I assume it has something to do with how SQL server parses the query, running ands before ors?

Is there a general rule of how to the best order of statements for ultimate optimisation?

Having run my own tests, the 2nd query knocks 0.5ms off the processing time. Its small, I know, but would increase with the complexity of the query (still makes almost no difference) and I have a general interest in how SQL server works.

Answer 1

It has nothing to do with the actual SQL execution. Unlike most languages you know, T-SQL does not have boolean operator short circuit¹ and concepts like 'running ands before ors' make no sense, the order of evaluation is entirely driven by the query optimizer decisions and the may end up being completely different from what you write, or what you expect.

The expression rewrite you see seems to be entirely an artifact of the SSMS query designed internal parser.

_{1 Or better said the boolean short circuit cannot be determined imperatively by how you write the expression. Actual run time operator short circuit does happen, you just can't know if and when it will happen.}

Answer 2

In general, there is no guarantee for how SQL Server evaluates the Where clause. The optimizer will always try to find the most efficient way to execute the query.

To determine execution order, you should get an execution plan for your query (which you can get via SSMS). However, keep in mind this plan may change depending on what the optimizer thinks is the best query given current various statistics and resources.