What is Sargable argument?

12

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

    
asked by anonymous 18.06.2014 / 17:52

1 answer

7

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

In short a predicate or condition is considered sargable (Search ARGument Able) if the DBMS can take advantage of an index to improve the execution time of a given query.

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

SELECT * 
FROM tbl_tabela
WHERE YEAR(ColunaData) = 2008 

In this situation it is not possible to use an index on the ColumnData column, even if it exists. This function will therefore be evaluated for each record in the table. An alternative to using 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:

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

Alternatively it could be used:

SELECT *
FROM tbl_tabela
WHERE ColumnVarchar LIKE 'Joaquim%'

For the final example I leave the NULLs treatment that we encounter almost daily

How not to do it:

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 

One final note. It should be noted that the sargability concept applies not only to the WHERE clause but also affects ORDER BY, GROUP BY, and HAVING. SELECT may already contain non-sargable expressions that have no impact on performance.

    
29.04.2015 / 19:40