From Wikipedia, the free encyclopedia
Jump to: navigation, search
WikiProject Computing / Software  
WikiProject icon This article is within the scope of WikiProject Computing, a collaborative effort to improve the coverage of computers, computing, and information technology on Wikipedia. If you would like to participate, please visit the project page, where you can join the discussion and see a list of open tasks.
 ???  This article has not yet received a rating on the project's quality scale.
 ???  This article has not yet received a rating on the project's importance scale.
Taskforce icon
This article is supported by WikiProject Software.

Renewing questioning[edit]

Why is this here? It's database specific and there is no indicator that this terms has any substantial impact or use. Evan Carroll (talk) 23:00, 18 January 2017 (UTC)

I found a link to this article from the outside world and found it to be a super useful explanation -- I'd argue not to delete the page because it may be the best definition/explanation of the term. (Aaron Powers) 16 February 2017 — Preceding unsigned comment added by (talk) 19:16, 16 February 2017 (UTC)

Older stuff (probably needs archiving)[edit]

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 (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 and for SQL Server examples. Mind, I won't necessarily argue with the 'Brain-dead' characterization of query optimizers, especially at 3 AM. (talk) 14:01, 28 August 2013 (UTC)OlGrizz
Those two pages you mentioned do not point out any difference between the left side and the right side of a comparison in a WHERE clause. They both point out differences between using functions and not using them. Razvan Socol (talk) 06:58, 2 September 2013 (UTC)

"IN" and "NOT IN" not sargable?[edit]

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. (talk) 01:29, 30 July 2012 (UTC)

Doubtful example[edit]

I find this example weird:


Unless there is an index on REVERSE(Email) ??

Also a comment describes the article as MSSQL specific, whereas some queries are using MySQL syntax. — Preceding unsigned comment added by Alain tesio (talkcontribs) 19:58, 20 February 2015 (UTC)

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)

Name of article[edit]

Seems, this page should be renamed to "SARGability" according common rules Raoul NK (talk) 15:37, 27 July 2015 (UTC)

The term "sargable" seems to be mainly MSSQL lingo, I've never heard it anywhere else. I think it should be something more vendor-neutral. The best I could think of at the moment was "Indexable operator (SQL)". Ideas? -- intgr [talk] 21:36, 27 July 2015 (UTC)
Maybe, although the term "sargable" isn't patented and is clear to comprehend. "Indexable operator" is obscure and could cause a confusion. Maybe, "sargable operator" or even "sargable comparision"? Raoul NK (talk) 07:39, 28 July 2015 (UTC)