Non-SARGable Predicates

When you write queries and you want them to be fast, your goal should be to write the simplest query you can.

Unfortunately, what’s simple to you may be difficult for the optimizer, and for your query. Some examples of this are:

• function(column) = something
• column + column = something
• column + value = something
• column = @something or @something IS NULL
• column like ‘%something%’
• column = case when …

And when you do stuff like this, your queries can end up with all sorts of bad side effects:

• Increased CPU
• Index Scans (when you could have Seeks)
• Implicit Conversion
• Poor Cardinality Estimates
• Inappropriate Plan Choices
• Long Running Queries

Microsoft built a lot of data formatting functions into T-SQL, and people have made the unfortunate choice to use data formatting functions in relational places, like joins and where clauses. It gets even worse when you stick user defined functions in there. There are some special rules for CONVERT when used with dates but they’re also not perfect.

For more reading, check out these posts:

• Sargability: Why %string% Is Slow
• Optional Parameters and Missing Index Requests
• Computed Columns: Reversing Data For Easier Searching
• Do I Have A Query Problem Or An Index Problem?
• If You Can’t Index It, It’s Probably Not SARGable
• Can Non-SARGable Predicates Ever Seek?

Time so start digging into your code to look for some of these anti-patterns, and ways to fix them.

Some things that can help:

• Computed columns
• #Temp Tables
• … Or just fix the underlying data!

You can use these constructs to persist data so that you don’t have to calculate them in-flight when the query runs.