Making non-SARGable queries seek, without changing the query
Tags: sql, database, sqlserver, performance
Published: 2020-05-07 05:54:47

---
title: Making non-SARGable queries seek, without changing the query
published: true
date: 2020-05-06 12:36:27 UTC
tags: SQL,databases,sqlserver,Performance,
canonical_url: https://am2.co/2020/05/making-non-sargable-queries-seek-without-changing-the-query/?utm_source=rss&utm_medium=rss&utm_campaign=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](https://dba.stackexchange.com/a/217983/52344). You’ve read about how [this can be a problem](https://www.erikdarlingdata.com/2019/04/slightly-less-not-sargable/), and [how bad it is for performance](https://www.erikdarlingdata.com/2019/04/slightly-less-not-sargable/).

[![](http://am2.co/wp-content/uploads/sarg-pubs-featured-1024x577.png)](https://www.youtube.com/watch?v=tALDcVmWE9w)

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](https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/northwind-pubs) 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:

```sql
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.  
 ![](http://am2.co/wp-content/uploads/sarg-pubs-1.png)

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:

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

And now my query does a seek on the index, as expected!  
 ![](http://am2.co/wp-content/uploads/sarg-pubs-2.png)

## 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](https://www.sentryone.com/blog/aaronbertrand/bad-habits-revival), like [using `SELECT *` or doing an `INSERT` without including the column list](https://sqlblog.org/2009/10/10/bad-habits-to-kick-using-select-omitting-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.

```sql
ALTER TABLE dbo.employee 
     ADD lname_firstchar AS LEFT(lname,1);
CREATE INDEX ix_employee_lname_firstchar 
 BbbbON dbo.employee(lname_firstchar);
```

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

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

We get a beatifull index seek on our new index:  
 ![](http://am2.co/wp-content/uploads/sarg-pubs-3.png)

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.

The post [Making non-SARGable queries seek, without changing the query](https://am2.co/2020/05/making-non-sargable-queries-seek-without-changing-the-query/) appeared first on [Andy M Mallon - AM²](https://am2.co).