Sargable vs non-sargable query

What are sargable and non sargable queries? A query is considered to be sargable if it’s able to take advantage of an index to speed up the execution of a query. A non sargable query is the opposite of that where the dbms engine is unable to take advantage of an index.

Person table

Let’s say there’s a Person table and an index with the following SQL:

CREATE TABLE Person (  
    ID int,  
    LastName varchar(255),  
    FirstName varchar(255),  
    Address varchar(255),  
    City varchar(255),
    CONSTRAINT PK_Person_ID PRIMARY KEY NONCLUSTERED (ID)
);

CREATE INDEX IX_Person_FirstName on dbo.Person(FirstName);

Sargable query example

An example of a sargable query is when you query on a column that is already indexed. Say ID is a primary key on a table called Person, then the following query would be considered sargable.

SELECT * FROM dbo.Person where ID = 5;

Since the query is using the primary key, the index can be used. Another exmaple would be to query by the first name, which would use the index IX_Person_FirstName.

SELECT * FROM dbo.Person where FirstName = 'Sam';

The above query would also use the index and hence be called a sargable query.

Non-sargable query

A non-sargable query would be a query that would not use an index when calculating the results. For example, a query on the LastName would be considered a non-sargable query because the dbms engine would not be able to use an index to help speed up execution of the query.

SELECT * from dbo.Person where LastName = 'Tambunan';

The above query would be considered a non-sargable query and can run quite slow if the table becomes big enough.

While that seems simple enough, one fun gotcha to watch out for is that a query can be formulated incorrectly that it becomes a non-sargable query. If someone asks Get me a list of persons that have an empty first name, and if it doens't exist (null), assume it's empty.

A SQL of the following can then be formulated:

SELECT * from dbo.Person where ISNULL(FirstName, '') = '';

The problem with this is that ISNULL() is used on FirstName which makes it non-sargable since the DBMS engine would have to run the ISNULL() function on the FirstName column and then do the comparison.

A better approach would be to do:

SELECT * from dbo.Person where FirstName = '' OR FirstName IS NULL;

This would ensure that the index is used and would be considered a sargable query.

Proper way of using IsNull

A better use of ISNULL would be to transform the results and not to use it on the where clause. The DBMS would still be able to use the index for filtering and would then be able to replace null values of FirstName with NoName

SELECT ISNULL(FirstName, 'NoName') as FirstNameOrEmpty from dbo.Person where FirstName is NULL or FirstName = 'John';