Erik Darling continues a series on SARGability. First up, max datatype columns aren’t going to cut it:
No matter how much you scream, holler, and curse, when you try to filter data in a column with a max type, that predicate can’t be pushed to when you touch the index.
Leaving aside that max data types can’t be in the key of an index, because that would be insane, even if you stick them in the includes you’re looking at a lot of potential bulk, and not the good kind that makes you regular.
Read on for an example of Erik’s point, and then go to the next post, which covers fixing functions:
The bottom line on scalar UDFs is that they’re poison pills for performance.
They’re bad enough in the select list, but they get even worse if they appear in join or where clause portions of the query.
The example query we’re going to use doesn’t even go out and touch other tables, which can certainly make things worse. It does all its processing “in memory”.
Both of these are worth checking out.