sargable

SARGable stands for search argumentable and refers to whether a predicate in a SQL statement can be evaluated using an index. A query is considered sargable when the database optimizer can apply an index seek or a range scan to locate matching rows efficiently, rather than scanning every row in a table.

Most common sargable predicates are simple comparisons on indexed columns, such as column = value, column > value, column >= value, or column BETWEEN low AND high. In joins and WHERE clauses, including such predicates on the leading column of an index helps the optimizer use the index to locate results quickly.

Non-sargable predicates wrap the indexed column in a function or expression, or perform operations that prevent the index from being used. Examples include WHERE YEAR(date_col) = 2020, WHERE LEFT(name, 3) = 'Bar', or WHERE col * 2 = 100. Implicit type conversions can also hurt sargability if the constant or the column type triggers a conversion that blocks index usage. In these cases, the optimizer may opt for a full table scan or fewer efficient strategies.

Practical guidance to improve sargability includes rewriting queries to avoid applying functions to columns, ensuring literals use appropriate types, and considering computed persisted columns with indexes. Additionally, sound indexing strategies, up-to-date statistics, and, where appropriate, partitioning can enhance overall query performance by preserving or enabling sargable predicates.