Making non-SARGable queries seek, without changing the query

We’ve all been there. You’ve got a query where the JOIN or WHERE predicate is not SARGable. You’ve read about how this can be a problem, and how bad it is for performance.

Alas, you cannot change the query. Sometimes this reason is political, sometimes it’s because you’ve got a third-party app and simply don’t have access to the code. But you do have access to the database…

Bad query, good query

I’m going to use the pubs database for this example, because I love low laughably small it is.

First, let’s look at that non-SARGable query that is causing all sorts of performance problems:

SELECT lname
FROM pubs.dbo.employee
WHERE LEFT(lname,1) ='J';

The plan for this does a table scan, because that LEFT() function prevents using any index.

If I were able to re-write the query, I could simply change that so that the function call isn’t wrapping the column name at all, in this case, using a LIKE expression:

SELECT lname
FROM pubs.dbo.employee
WHERE lname LIKE 'J%';

And now my query does a seek on the index, as expected!

But what if you can’t change that query?

So, let’s go back to the problem of a third-party application where I physically can’t change the query.

What if I can change the table?
DISCLAIMER: If the application developer has non-SARGable predicates causing problems, they might also have other bad habits, like using SELECT * or doing an INSERT without including the column list. You’ll need to test this option to make sure you don’t fix this query and break a different one.

We’re going to add a computed column, and index that computed column. We aren’t persisting the computed column itself–that would make it live in the clustered index with all the other columns, and we don’t want that. We’re going to create a non-clustered index so we can seek on the computed values–essentially manually persisting the column.

The important thing here is that the function we use in our index must match the function used in the query. If the function changes, we’re going to have to create a new computed column & index.

ALTER TABLE dbo.employee
    ADD lname_firstchar AS LEFT(lname,1);

CREATE INDEX ix_employee_lname_firstchar ON dbo.employee(lname_firstchar);

Now, when we go back & run our original, non-SARGable query:

SELECT lname
FROM pubs.dbo.employee
WHERE LEFT(lname,1) ='J';

We get a beatifull index seek on our new index:

Now, I wouldn’t use this technique intentionally. I’d always prefer to re-write the query so I don’t have to maintain that extra index. But in response to an emergency problem while you develop/deploy a code fix, or argue with your vendor to fix their code, this can be a great way to save the day when you can’t fix the query.

--===== Do the same thing as in the article but on the Adventureworks database. USE AdventureWorks ; ALTER TABLE Person.Person ADD lname_firstchar AS LEFT(LastName,1) ; CREATE INDEX ix_lname_firstchar ON Person.Person(lname_firstchar) --INCLUDE (LastName) ; --===== Run the test query. Unless you use the INCLUDE above, -- it still results in a SCAN SELECT LastName FROM Person.Person WHERE LEFT(LastName,1) ='J' ; --===== Put things back to normal DROP INDEX ix_lname_firstchar ON Person.Person ; ALTER TABLE Person.Person DROP COLUMN lname_firstchar ;

Jeff Moden
May 9, 2020 at 2:36 pm
Thanks for taking the time to post this, Andy.

Just a reminder that, as with all else in SQL Server, “It Depends”.

If we do exactly the same thing on the Adventureworks database, it still results in a scan with an index hint to add an INCLUDE to the index. Here’s the code (and I’m using SQL Server 2017 Developers Edition)…
```
--===== Do the same thing as in the article but on the Adventureworks database.
    USE AdventureWorks
;
  ALTER TABLE Person.Person
    ADD lname_firstchar AS LEFT(LastName,1)
;
 CREATE INDEX ix_lname_firstchar ON Person.Person(lname_firstchar) 
--INCLUDE (LastName)
;
--===== Run the test query.  Unless you use the INCLUDE above,
     -- it still results in a SCAN
 SELECT LastName
   FROM Person.Person
   WHERE LEFT(LastName,1) ='J'
;
--===== Put things back to normal
   DROP INDEX ix_lname_firstchar ON Person.Person
;
  ALTER TABLE Person.Person
   DROP COLUMN lname_firstchar
;
```
- Andy
  May 9, 2020 at 3:01 pm
  
  I picked my example because lname is in the clustered index in pubs, so it makes the example as simple as possible for the demo. 😊 But yes–all the usual rules for indexes still apply. If you want the index to be covering, you’ll need to make sure that all the referenced columns are part of the index structure.