Talk:Sargable

Renewing questioning

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 144.212.3.4 (talk) 19:16, 16 February 2017 (UTC)

Agree that the term is in use. For example, here’s a conference article that uses the term “SARGable” in its text: Lang, Harald; Mühlbauer, Thomas (2016). Data Blocks: Hybrid OLTP and OLAP on Compressed Storage using both Vectorization and Compilation (PDF). SIGMOD 2016, San Francisco. pp. 311–326. doi:10.1145/2882903.2882925.{{cite conference}}: CS1 maint: location (link) . —Sascha (talk) 08:03, 8 January 2024 (UTC)

This term is extremely valuable to those learning RDBMS technology. Understanding when an index can be utilized and when it cannot be is vital to this day, and is a stumbling block that cause many inefficiencies in modern SQL environments. I direct students here frequently to get a basic understanding of SARGability before I go on to educate them on the specific implications. The article could use some help, but is still of vital importance.199.88.54.180 (talk) 17:32, 2 March 2022 (UTC)

Older stuff (probably needs archiving)

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 85.116.198.153 (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. 216.26.134.251 (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)

I believe that the OP of this threat missed the point of the article. You can always transform the data in one column into a new column ('foo' -> REVERSE('foo')) and index that new column. This is impractical in many cases, as it requires you to consider every possible way that your data can be transformed and create indexes for these. The purpose of SARGability is to establish an understanding of when an index on a column will cease to function. The article does a good job of explaining this in simple terms. It could use, perhaps, more examples, but this is still a vital skill that DBAs and DB Devs need to build highly optimized queries. Suggesting that there is a solution to the issues of non-SARGable queries doesn't mean that the term or the article are now obsolete; even in 2022 this article is an important source of knowledge for an obscure term that is extremely important to the field of RDBMS'.

Also; About having columns on the left and scalars on the right in a comparison: This was the author injecting a comment on proper syntax. In no way is this required, but it is generally considered a best practice and is in play just about anywhere I have seen such comparisons.199.88.54.180 (talk) 17:41, 2 March 2022 (UTC)

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

"IN" operator usually will do an index scan (cheap), optional merge (union); whereas "NOT IN" can sometimes force reading the whole table. Hansschulze (talk) 01:01, 9 January 2020 (UTC)

Doubtful example

I find this example weird:

Sargable: SELECT ... WHERE REVERSE(Email) LIKE REVERSE('%wikipedia.org')

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 (talk • contribs) 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

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)

I agree it's mainly SQL Server, but it is a term of art among people who write database query optimizers (across many companies). Also, I found uses in IBM [1], Sybase [2] and Oracle [3] end-user documentation. Julianhyde (talk) 22:33, 17 October 2017 (UTC)

SARGABLE Query might be a good name. I have seen this elsewhere. But there are references outside to Sargable.

Newer tools are more immune to SQL newbies, and know how to fix some of these bad operators. Sargable doesn't imply indexable, but more the attitude towards the mathematical expression of work that the RDBS does. Hana (in memory), for example, blew away all SARGABLEs and did a query in 1/10 the time of MS SQL, using MSSQL format query, which is the de-facto current dialect based on the days of COBOL. Take BASIC as an example. We no longer refer to Tandy BASIC or MS BASIC, but more commonly VBA or Visual Basic. We won't need this page when natural language queries are more common, and order of operations rules are wisked away. I will try to add more to this page. Hansschulze (talk) 01:18, 9 January 2020 (UTC)