|WikiProject Computing / Software|
Just stumbled upon this ... and the examples just don't make any sense. It's just some random selection of predicates that may or may not be possible to look up using an index with some specific version of some specific RDBMS, but there is nothing fundamentally preventing an RDBMS from using an index for looking up, say, LIKE '%foo'. If it's possible to create an index that can be used for looking up REVERSE(x) LIKE 'oof%', you obviously can create one for looking up x LIKE '%foo'.
Also, you obviously can create indices on any functional mapping of a record, such as Year(), isNull(), or SUBSTRING(). And more generally, if a human can do some algebraic transformation on a predicate, then so can an RDBMS, so for any two predicates of which one is sargable and the other can be algebraically transformed into the first, it follows that second one can in principle be looked up by the RDBMS using some kind of index as well, which covers all of the examples given. It's just a matter of which specific optimizations the respective RDBMS implements whether it will figure it out or not.
Also, the rule "functions in the left part of a sql condition." doesn't make any sense. There isn't any such thing as a "left part of a sql condition.", and in any case there shouldn't be any fundamental difference between sides of (commutative) relation operators with any not completely braindead RDBMS. booleancolumn doesn't have any sides and is a perfectly sensible predicate. func(column) = 'foo' and 'foo' = func(column) should result in exactly the same execution plan with even the most simple-minded optimizers. — Preceding unsigned comment added by 22.214.171.124 (talk) 03:03, 14 June 2012 (UTC)
- <Real World Intrusion Alert>
- RE: "... the rule "functions in the left part of a sql condition." doesn't make any sense.": This may be a collision between relational theory and actual experience with commercial databases, in particular with the query optimizers of commercial databases.
- Sadly, query optimizers do indeed pay attention to rvalue vs. lvalue position in a WHERE statement. Check out http://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/ and http://blog.sqlauthority.com/2013/03/12/sql-server-avoid-using-function-in-where-clause-scan-to-seek/ for SQL Server examples. Mind, I won't necessarily argue with the 'Brain-dead' characterization of query optimizers, especially at 3 AM. 126.96.36.199 (talk) 14:01, 28 August 2013 (UTC)OlGrizz
"IN" and "NOT IN" not sargable?
I am confused by why "IN" and "NOT IN" would not be sargable. Either the article is wrong, or the "IN" / "NOT IN" situation needs to be explained in more detail.
Isn't "col IN (1,2,3)" just syntax sugar for "col = 1 OR col = 2 OR col = 3"? One of the examples ("WHERE ((FullName = 'John Smith') OR (FullName IS NULL))") shows that the "OR" logical disjunction is sargable. 188.8.131.52 (talk) 01:29, 30 July 2012 (UTC)
I find this example weird:
Sargable: SELECT ... WHERE REVERSE(Email) LIKE REVERSE('%wikipedia.org')
Unless there is an index on REVERSE(Email) ??
- In an effort to be less MSSQL-specific, I tweaked some of the examples to use standard(ish) SQL syntax; they should work on more than one database now (MSSQL not necessarily among them, different DBs have implemented a different subset of standard syntax). But the rest of the article is still about MSSQL behaviors. -- intgr [talk] 17:04, 17 April 2015 (UTC)