What is Sargable argument?

Question

In database queries what is the concept of a sargable argument (Search Argument Able)?

Answer 1

I know this question is almost a year old but I think that maybe the answer might interest someone.

Briefly a predicate or condition is considered sargable (Search Argument Able) if DBMS can take advantage of an index to improve the execution time of a particular query.

A typical situation that makes a query non-sargable is to include in the WHERE clause a function which has as a parameter a column. For example:

SELECT * 
FROM tbl_tabela
WHERE YEAR(ColunaData) = 2008 

In this situation it is not possible to use an index on the column Colunadata, even if it exists. This function will therefore be evaluated for each record in the table. An alternative to use would be:

SELECT * 
FROM tbl_tabela
WHERE ColunaData >= '01-01-2008' AND ColunaData < '01-01-2009'

Another example that is often used:

SELECT *
FROM tbl_tabela
WHERE Field LIKE '%blah%'

Or else:

SELECT *
FROM tbl_tabela
WHERE SUBSTRING(ColumnVarchar, 1, 6) = 'Joaquim'

Alternatively it could be used:

SELECT *
FROM tbl_tabela
WHERE ColumnVarchar LIKE 'Joaquim%'

For final example I leave the treatment of NULL which we encounter almost daily

How not to:

SELECT *
FROM tbl_Tabela
WHERE ISNULL(ColunaNome, 'Manuel Joaquim') = 'Manuel Joaquim'

Correct version:

SELECT *
FROM tbl_tabela
WHERE ColunaNome= 'Manuel Joaquim' 
   OR ColunaNome IS NULL 

A final note. It is noteworthy that the concept of sargability applies not only to the WHERE clause but also to ORDER BY, GROUP BY and HAVING. SELECT can contain expressions non-sargable that this has no impact on performance.