Can Non-SARGable Predicates Ever Seek?

Last Updated 6 years ago

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.

CREATE INDEX ix_age 
	ON dbo.Users(Age) INCLUDE (Reputation) 

SELECT SUM(u.Reputation) AS whatever
FROM   dbo.Users AS u
WHERE  u.Age = 0
OR     u.Age IS NULL;

1

2

3

4

5

6

7

CREATE INDEX ix_age

ON dbo.Users(Age) INCLUDE (Reputation)

SELECT SUM(u.Reputation) AS whatever

FROM dbo.Users AS u

WHERE u.Age = 0

OR u.Age IS NULL;

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.

SELECT SUM(u.Reputation) AS whatever
FROM   dbo.Users AS u
WHERE  ISNULL(u.Age, 0) = 0;

1

2

3

SELECT SUM(u.Reputation) AS whatever

FROM dbo.Users AS u

WHERE ISNULL(u.Age, 0) = 0;

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.

ALTER TABLE dbo.Users 
	ADD AgeNoNull AS ISNULL(Age, 0);

CREATE INDEX ix_agenonull 
	ON dbo.Users(AgeNoNull) INCLUDE (Reputation);

1

2

3

4

5

ALTER TABLE dbo.Users

ADD AgeNoNull AS ISNULL(Age, 0);

CREATE INDEX ix_agenonull

ON dbo.Users(AgeNoNull) INCLUDE (Reputation);

Which means our original query now gets a Seek plan!

SELECT SUM(u.Reputation) AS whatever
FROM   dbo.Users AS u
WHERE  ISNULL(u.Age, 0) = 0;

1

2

3

SELECT SUM(u.Reputation) AS whatever

FROM dbo.Users AS u

WHERE ISNULL(u.Age, 0) = 0;

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

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.

4 Comments. Leave new

Andrew H
June 19, 2018 12:24 am

extra performance bonus points is that the seek is ordered output, making use of this in a sub-query or join much cheaper (assuming you’ve another table with the relevant key on hand, ordered).

Reply
José Diz
October 6, 2018 5:43 pm

Erik, correct the WHERE clause in the code that is immediately after “Seekaroni” when possible. There is a losted parenthesis there.
Replace
WHERE ISNULL(u.Age, 0) = 0);
by
WHERE ISNULL(u.Age, 0) = 0;
—
José Diz
https://portosql.wordpress.com

Reply
- Brent Ozar
  October 7, 2018 7:27 am
  
  José – great catch, fixed!
  
  Reply
Construindo códigos T-SQL eficientes: Sargability – Porto SQL
October 7, 2018 11:58 am

[…] Can Non-SARGable Predicates Ever Seek? […]

Reply