1

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!

1
3

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:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.