Rule Definition
non-SARGable WHERE clauses are performances killers : for a non-SARGable query, the query optimizer has to scan all the rows in the table even if that column has an index.
Remediation
Check the statement and if so, replace non-SARGable predicates with SARGable.
Violation Code Sample
-- Index scan (slow)
WHERE UPPER(ColumnValue) = 'SUMMER' -- not sargable predicate
------------
-- Table scan or index scan on a primary key index
-- no index use on ColumnValue
WHERE SUBSTRING(ColumnValue,1,3) = 'sum' -- not sargable predicate
------------
-- Table scan - no index use on ColumDate
WHERE year(ColumDate) = 2017 and month(ColumDate) = 1 -- not sargable predicate
------------
-- not sargable predicate due to function use
-- Table scan
WHERE floor(ColumnValue) = 50 -- not sargable predicate
Fixed Code Sample
-- Index scan (slow)
WHERE UPPER(ColumnValue) = 'SUMMER' -- not sargable predicate
-- Index seek (fast)
WHERE ColumnValue = 'Summer' -- sargable predicate
------------
-- Table scan or index scan on a primary key index
-- no index use on ColumnValue
WHERE SUBSTRING(ColumnValue,1,3) = 'sum' -- not sargable predicate
-- Index seek if there is an index on ColumnValue
WHERE ColumnValue LIKE 'sum%' -- sargable predicate
------------
-- Table scan - no index use on ColumDate
WHERE year(ColumDate) = 2017 and month(ColumDate) = 1 -- not sargable predicate
-- Index seek
WHERE ColumDate >='2017-01-01' and ColumDate < '2017-02-01' -- sargable predicate
------------
-- not sargable predicate due to function use
-- Table scan
WHERE floor(ColumnValue) = 50 -- not sargable predicate
-- Index seek if there is an index on ColumnValue
WHERE ColumnValue = 50 -- sargable predicate
Reference
http://dotnetvj.blogspot.com/2010/02/sargable-vs-non-sargable-queries.html
Related Technologies
Technical Criterion
Efficiency - SQL and Data Handling Performance
About CAST Appmarq
CAST Appmarq is by far the biggest repository of data about real IT systems. It's built on thousands of analyzed applications, made of 35 different technologies, by over 300 business organizations across major verticals. It provides IT Leaders with factual key analytics to let them know if their applications are on track.