sargable vs non-sargable queries

A condition in a query is said to be sargable if the SQL Server can take the advantage of an index to speed up the execution of the query (using index seeks, not covering indexes). sargable stands for Search ARGument Able.

Non-sargable search arguments in the WHERE clause are:
IS NULL
"<>"
NOT
NOT EXISTS
NOT IN
NOT LIKE
LIKE '%something'


In a query if you have mix of Non-sargable and Sargable conditions then SQL Server might use covering index based on the columns used in SELECT Clause, WHERE Clause and JOIN clause. These covering indexes sometimes may not be appropriate to the query and can increase disk I/O. In adidtion to this, try to avoid using any functions on Indexed columns.

Non-sargable Query:
SELECT FirstName,LastName FROM Person.Person WHERE LEFT(LastName,2) = 'Ma'

Sargable Query:
SELECT FirstName,LastName FROM Person.Person WHERE LastName LIKE 'Ma%'


Both of these queries produce the same result but the first one is non-sargable and will run little slow compared to the second one which is sargable.

Reference : Vijaya Kadiyala (www.DotNetVJ.com)