Non-Sargable is Self Sabotage

Have you ever seen or heard of a query being sargable? No, there is no military relation (hopefully I’m not the only one who usually thinks “Sargent”).

The term sargable is a shortened way to say “search argument able.” Queries must be written in such a way that they can take advantage of the indexes in place to support them. This is what makes a query sargable. Let’s look at an example changing a query from non-sargable into sargable.

Going Too Wild

Don’t shoot yourself in the foot with wildcard characters that you don’t need. One mistake that I’ve seen that has an easy fix revolves around using LIKE ‘%String%’ when you really only need ‘String%’.

Let’s say we have a database with a table of comic books. We only want to search for titles and issues in the table where the title starts with “Batman.” Even though we only care about titles that start with Batman, I still see queries that look like this:

SELECT Title, Issue FROM Comic
WHERE Title LIKE '%Batman%';
GO

I’ve seen many make the mistake of unnecessarily surrounding the search string with wildcards. If we check the execution plan, we see an index scan on the clustered index.

Full Potential

We might think to create a nonclustered index to optimize our query performance. We’ll create an index on Title and include Issue:

CREATE INDEX ComicTitleIncludeIssue ON Comic (Title) 
INCLUDE (Issue);

If we run our query again, we will see the nonclustered index was used but still not as an index seek.

The plan has an index scan because our query is not sargable. Since we’re looking on both sides of Batman, we still have to scan every title in the table. If we’re only checking where the title begins with Batman (which is what we wanted anyway) then we’ll get a different plan:

SELECT Title, Issue FROM Comic
WHERE Title LIKE 'Batman%';
GO

There’s our index seek. We also see the difference when running both SELECT statements together:

The second query that has an index seek is clearly less costly and only returns the data we wanted to retrieve.

Thanks for reading!