April 10, 2024

Search Arguments

When a query predicate is written such that it can be serviced by an existing index, then the predicate is a search argument, or, said another way, the predicate has the property of being sargable. Sargability is one of the things to check for when tuning a query because it is possible to write a query in a way that prevents the optimizer from using an index. Rewriting the predicate to be sargable can increase performance because now the optimizer can take advantage of an index (assuming one exists).

What makes a predicate sargable (or not)?

For a predicate to be sargable the column must not be manipulated in a way that turns it into an expression. For example, wrapping the column in a function or doing arithmetic on the column. Example:

SELECT SalesOrderID 
FROM Sales.SalesOrderHeader 
WHERE SalesOrderID + 0 = 43659

Here the left hand side of the predicate in the WHERE clause is an expression (SalesOrderID + 0) and this means that SQL Server cannot use the clustered index PK_SalesOrderHeader_SalesOrderID efficiently by seeking into it; instead it must scan the whole index:

index scan

Now tune the query by removing the + 0 (which doesn't actually do anything apart from makes things harder for the optimizer):

SELECT SalesOrderID 
FROM Sales.SalesOrderHeader 
WHERE SalesOrderID = 43659

See in the execution plan how the optimizer can seek into the index, which is more efficient:

index seek

Things that prevent predicates being sargable

It's basically about leaving the predicate column in the WHERE clause alone and not turning it into an expression by:

Using the column in a function: e.g. ISNULL(column, -1) = -1
Doing maths on the column: e.g. value + column = something as shown above
Having a WHERE clause of the form: column = @something OR @something IS NULL
Using a leading wildcard: e.g. column LIKE '%something%'
Predicates containing CASE expressions: column = CASE WHEN...
Concatenating: e.g. column + column = something

When does sargability matter?

Sargable only matters when an index exists on the predicate column. Otherwise it doesn't matter. For example:

--sargable but no index
SELECT ShipDate
FROM Sales.SalesOrderHeader 
WHERE ShipDate = '2011-06-07 00:00:00.000'

--non-sargable
SELECT ShipDate 
FROM Sales.SalesOrderHeader 
WHERE DATEADD(DAY, 0, ShipDate) = '2011-06-07 00:00:00.000'

Both of the above queries use a clustered index scan to satisfy the query. Even though the top query has a (potentially) sargable predicate, it doesn't matter because there isn't an index on ShipDate.

Selectivity

If a predicate is selective, i.e. it matches on a small amount of rows, then a sargable predicate can greatly improve performance because the optimizer can choose to efficiently seek into the index to obtain those few rows instead of scanning the whole index.

Summary

Making query predicates sargable in one of the tools in the query tuning toolbox. It's done by ensuring appropriate indexes exist and by not manipulating the predicate column in a way that prevents the optimizer from using the index. Sargable queries run faster so it's a best practice to write sargable queries from the start, even if an index doesn't exist (yet). I can't think of a reason why you wouldn't.