Why can't an OR operation or an OR condition be rejected?

Question

In the literature, I read that using an OR clause or operator in a WHERE clause makes a statement non-sargable. I do not know why and how this can be true. Any help would be appreciated.

Answer 1

The answer was provided by the author of the authoritative book of SQL Server Query Performance Tuning optimization , Grant Fritchey. So here it is:

"The OR operators are much more optimized than before, but if you think about it, if I have an index and I want to match values ​​equal to A or Z, the engine should do some comparisons, not just 1. Full-fledged conditions lead to a direct searching for a point or finding a range. Thus, A will go around the tree and extract one row or set of rows from the index for this value. A or R, he cannot get the range, he must do other kinds of work. Sometimes you will see that they are executed as two queries using the JOIN operation. that’s great. But sometimes you’ll see additional filter operators or scans. not routine. " (again, the credit belongs to the author)