Any1<\/sup> function on a column will prevent an index seek from happening, even if the function would not change the column\u2019s value or the way the operator is applied, as seen in the above case. Zero added to an integer doesn\u2019t change the value of the column, but is still sufficient to prevent an index seek operation from happening.<\/p>\n While I haven\u2019t yet found any production code where the predicate is of the form \u2018Column + 0\u2019 = @Value\u2019, I have seen many cases where there are less obvious cases of functions on columns that do nothing other than to prevent index seeks.<\/p>\n UPPER(Column) = UPPER(@Variable) in a case-insensitive database is one of them, RTRIM(COLUMN) = @Variable is another. SQL ignores trailing spaces when comparing strings.<\/p>\n The other requirement for a predicate to be SARGable, for SQL Server at least, is that the column and expression are of the same data type or, if the data types differ, such that the expression will be implicitly converted to the data type of the column.<\/p>\n There are some exceptions here. Comparing a DATE column to a DATETIME value would normally implicitly convert the column to DATETIME (more precise data type), but that doesn\u2019t cause index scans. Neither does comparing an ascii column to a unicode string, at least in some collations.<\/p>\n In generally though, conversions should be explicit and decided on by the developer, not left up to what SQL server decides.<\/p>\n What about operators?<\/p>\n The majority are fine. Equality, Inequality, IN (with a list of values), IS NULL all allow index usage. EXIST and IN with a subquery are treated like joins, which may or may not use indexes depending on the join type chosen.<\/p>\n LIKE is a slight special case. Predicates with LIKE are only SARGable if the wildcard is not at the start of the string.<\/p>\n There are blog posts that claim that adding NOT makes a predicate non-SARGable. In the general case that\u2019s not true.<\/p>\n![\"Seek1\"](\"https:\/\/www.sqlinthewild.co.za\/wp-content\/uploads\/2016\/09\/Seek1.png\" "\\"Seek1\\"")
<\/a><\/p>\nSELECT * FROM Numbers\r\nWHERE Number + 0 = 42;<\/pre>\n
<\/a><\/p>\nSELECT * FROM Numbers\r\nWHERE Number = 42 + 0;<\/pre>\n
<\/a><\/p>\nSELECT 1 FROM SomeTable\r\nWHERE StringColumn = 0;<\/pre>\n
<\/a><\/p>\nSELECT 1 FROM SomeTable\r\nWHERE StringColumn = \u20180\u2019;<\/pre>\n
<\/a><\/p>\nSELECT 1 FROM SomeStrings\r\nWHERE ASCIIString LIKE 'A%'<\/pre>\n
<\/a><\/p>\nSELECT 1 FROM SomeStrings\r\nWHERE ASCIIString LIKE '%A'<\/pre>\n
<\/a><\/p>\nSELECT * FROM Numbers\r\nWHERE NOT Number > 100;<\/pre>\n
<\/a><\/p>\nSELECT * FROM Numbers\r\nWHERE NOT Number <= 100;<\/pre>\n
<\/a><\/p>\nSELECT * FROM Numbers\r\nWHERE NOT Number = 137;<\/pre>\n