When are ORs considered non-sargable predicates

Question

I was wondering if I could get a further explanation on non-sargable predicates concerning the OR. From your Brent Ozar's blog on non-sargable predicates, I understand that "column = @something OR @something IS NULL"

In my mind, this case seems to make sense as to why it would make things harder on the optimizer to use an index. However, some of my colleagues now have a tendency to believe that all ORs are bad, whether they are found in this non-sargable case or not.

Obviously, ORs aren't the most efficient logic to begin with. I can see why we would want to avoid using them. From my understanding, not all ORs fall into the category of non-sargable predicates. I just want to understand more when an OR is considered a non-sargable predicate and when it's not.

Could you please explain more in-depth on this matter?

Thanks!

Answer 1

I don't know if there is a way to broadly say "these OR queries are not SARGable and these ones are" other than applying your understanding of how index seeks work to each query or set of predicates.

Your colleagues are probably wise to be skeptical of disjunctions (OR predicates) in that general way, although of course not every disjunction is going to cause performance issues.

Here are some situations where disjunctions lead to query performance issues here on the site - even when indexes could have been used:

• OR in a WHERE clause
  • SQL Server Index Scan when using 'OR' operator
  • Optimizer not choosing index union plan
• OR in a JOIN
  • Why are [Seemingly] suitable indexes not used on a LEFT JOIN with OR

Solutions in those cases are to use query hints, or rewrite the query to use CROSS APPLY / or UNION.