Why is "OR operation" or OR condition non-sargable?

Question

In literature, I have read that using OR condition or operator in a WHERE clause makes a statement non-sargable. I am not sure why or how this could be true. Any help would be appreciated.

Answer 1

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

"OR statements are much more optimized now than they used to be. But if you think about it, if I have an index and I want to match values that are equal to A or Z, the engine has to do multiple comparisons, not simply one. The sargeable conditions all result in a straight forward point lookup, or range lookup. So = A will walk a tree and retrieve the one row, or the set of rows, from the index for that value. But if it’s A or R, it can’t retrieve a range, it has to do other types of work. Sometimes you’ll see these done as two seeks with a JOIN operation. And that’s great. But other times you’ll see additional filter operators or scans. So, it’s not sargeable." (again, credit goes to the author)