Can Non-SARGable Predicates Ever Seek?

Cheating At Candy Crush

The short answer is that yes, they can. But only with a little extra preparation.

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.

Most Common

Is the NULL replacement issue, I think.

If I had an index and query like this, life would be grand. I’d be able to perform two seeks into the index and only read ranges of rows that qualify.

Seekaroni

Life gets less grand if I change the query to this. We have to scan the entire index, replace null values with 0, and apply a predicate.

I’m sleepy.

This isn’t disastrous here, but I’d probably want to fix it.

Unless I couldn’t. This may be turd party code that I can’t alter.

The Workaround

We can cheat a little bit, by adding a computed column and indexing it.

Which means our original query now gets a Seek plan!

Iffy

If you compare the number of rows read between the Seek and Scan, the Seek does get just the rows it needs.

A pillar in the community

Surprises

I was a bit surprised that this worked out well. It’s a bit like function based indexes in other RDMBS platforms. It’s also nice that the expression matching portion of the optimizer was able to pick up on it easily.

Thanks for reading!

Brent says: indexed (not just persisted) computed columns are an awesome trick to have in your performance tuning bag. If you have trouble getting SQL Server to use ’em, read Paul White’s post about trace flag 176.

Previous Post
Why You Should Stop Depending On SQL Server Versions In Code
Next Post
[Video] Office Hours 2018/6/13 (With Transcriptions)

4 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}