CODEWITHSUNDEEP
sqlsql-server
Arun Kumar T
Arun Kumar T

Reputation: 640

"AND" operation execution methodology in SQL Server

I searched a lot for this simple operation. But I could not get an answer. I have a simple query:

select column1, column2... 
from table1 
where condition1 and condition2 and condition3

The above query will returns some rows if all the conditions are true.

My question is whether it will give result as 'Fail' once it found the first condition (condition1) is failed?

The execution of rest of the operations is meaning less and it will take more time if the second and third conditions have huge operations.

Can you please explain this operations?

Upvotes: 2

Views: 61

Answers (2)

Ovais Khatri
Ovais Khatri

Reputation: 3211

You may check execution plan, there is an option in SQL Server management studio.

Edit: Reference: How do I obtain a Query Execution Plan?

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270443

The operation that you are referring to is short-circuiting. As far as I know, most SQL databases do implement this. That is, they stop evaluating clauses when the result of the where condition is known.

This comes with a caveat though: they do not guarantee the order of evaluation. So, the optimizer might evaluate them in any order. Note: you should not worry about simple comparisons. The only issue of performance would come with the use of functions (or perhaps like expressions on long strings), especially user-defined functions.

If you think you are smarter than the optimizer (which is quite possible), you can use case:

where (case when not (condition1) then 0
            when not (condition2) then 0
            when not (condition3) then 0
            else 1
       end) = 1

However, I would discourage this for two key reasons:

  • It prevents the optimizer from using an index.
  • The code is much less understandable.

In general, these types of optimizations are not important for query performance. Other issues, such as having well-defined indexes and proper data types are usually much more important.

Upvotes: 3

Related Questions