Hi All,
We always listen the words like sargable, non- sargable in performance tuning. Below is the small understanding of those words
Queries which avoid WHERE clauses are non-sargable. If a WHERE clause is sargable, this means that it can take advantage of a useful index (assuming one is available) to speed completion of the query.
If a WHERE clause is non-sargable, this means that the WHERE clause (or at least part of it) cannot take advantage of an index, instead performing a table/index scan, which may cause the query’s performance to suffer.
Non-sargable search arguments in the WHERE clause, such as “IS NULL”, “<>”, “!=”, “!>”, “!<”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%500’” generally prevents (but not always) the query optimizer from using a useful index to perform a search.
In addition, expressions that include a function on a column, expressions that have the same column on both sides of the operator, or comparisons against a column (not a constant), are not sargable.
But not every WHERE clause that has a non-sargable expression in it is doomed to a table/index scan. If the WHERE clause includes both sargable and non-sargable clauses, then at least the sargable clauses can use a useful index (if one exists) to help access the data quickly
I will send the detailed explanation on this with example in next article.
Regards,
Chaitanya
SQL BLOGGING 