Erik Darling shows that you can create a computed, indexed column to make a non-SARGable predicate perform a seek operation:
Before I show you what I mean, we should probably define what’s not SARGable in general.
- Wrapping columns in functions: ISNULL, COALESCE, LEFT, RIGHT, YEAR, etc.
- Evaluating predicates against things indexes don’t track: DATEDIFF(YEAR, a_col, b_col), a_col +b_col, etc.
- Optional predicates: a_col = @a_variable or @a_variable IS NULL
- Applying some expression to a column: a_col * 1000 < some_value
Applying predicates like this show that you don’t predi-care.
They will result in the “bad” kind of index scans that read the entire index, often poor cardinality estimates, and a bunch of other stuff — sometimes a filter operator if the predicate can’t be pushed down to the index access level of the plan.
Read on for an example.