What makes a query SARGable?

What is SARGability?
The word SARGable comes from the term “Search ARGumentable”. This tells if an item can be found by using an index, provided such an index is available. Understanding sargability can really impact the  ability to write well-performing queries.

If a WHERE clause is not sargable, the Query Optimizer will ignore any indexes available on the predicate column and force an Index Scan. In other case when the predicate is sargable, the Query optimizer will use the indexes and may do an index seek to give better performance.

Lets consider few examples and see how the sargability could affect the query performance.

Scenario 1: Find the orders placed/salesman for the past week

The typical solution for this solution would look look like this using a datediff. 

SELECT SalesPersonID
	,count(salesorderid)
FROM SalesOrderHeader
WHERE datediff(day, OrderDate, getdate()) < 8
GROUP BY SalesPersonID

However the execution plans reveals that the query is resulting in an index scan.

Here is the alternate solution, which is performs efficient compared to the latter.

SELECT SalesPersonID
	,count(salesorderid)
FROM SalesOrderHeader
WHERE OrderDate > dateadd(DAY, - 8, getdate())
GROUP BY SalesPersonID

The execution plan is showing an index seek in this case.

Scenario 2: Find the last year’s order details

The typical solution is to use the year function as shown

SELECT Count(salesorderid)
FROM SalesOrderHeader
WHERE year(OrderDate) = 2013

The execution plan in this case too, shows a Index scan

Better solution would be to use a between clause.

SELECT Count(salesorderid)
FROM SalesOrderHeader
WHERE OrderDate BETWEEN '20130101' AND '20131231'

And here is how the execution plan look like after the change

 

There are much more scenarios where the SQL optimizer chooses to do an index scan, but can be optimized to give better performance. Here is a few predicates that are non sargable

• IsNull(Column,Default)=’Something’
  • Alternative : (Column =’Something’ or Column Is Null)
• Substring(Column,6) = ‘Value’
  • Column Like ‘Value%’ .  Remember Like ‘%Value%’ is not sargable

I hope the illustrations would give you a better idea to avoid non-sargable predicates and replace them with sargable equivalents.

Here is a connect ticket opened with Microsoft support team to make more functions SARGable. Feel free to vote for them if you feel that it would add value to the product.

References

What makes a SQL statement sargable?
SARGable functions in SQL Server