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
- 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.
What makes a SQL statement sargable?
SARGable functions in SQL Server