SQL Server users use the term "sargable". I'm wondering if there is an objective implementation-agnostic timeless definition for "sargable."

For instance, WHERE foo LIKE '%bar%' is said by many to be not sargable, but some RDBMSs are able to use indexes on such queries. What then does "not sargable" mean?

Other References

  • 4
    You might want to point out that your question is not about SQL Server but instead about the term "sargable". Your question only referenced SQL Server because it is not able to handle "%wordhere%" search predicates, whereas apparently other RDBMS are. – hot2use Mar 19 at 6:42

The term "sargable" was first introduced by P. Griffiths Selinger et al. in their 1979 paper "Access Path Selection in a Relational Database Management System", published by ACM. For non-ACM members there's a copy of that paper at http://cs.stanford.edu/people/chrismre/cs345/rl/selinger.pdf

The term is defined in this paragraph:

Both index and segment1 scans may optionally take a set of predicates, called search arguments (or SARGS), which are applied to a tuple before it is returned to the RSI2 caller. If the tuple satisfies the predicates, it is returned; otherwise the scan continues until it either finds a tuple which satisfies the SARGS or exhausts the segment or the specified index value range. This reduces cost by eliminating the overhead of making RSI calls for tuples which can be efficiently rejected within the RSS. Not all predicates are of the form that can become SARGS. A sargable predicate is one of form (or which can be put into the form) "column comparison-operator value". SARGS are expressed as a boolean expression of such predicates in disjunctive normal form.

In other words, a sargable predicate is such that can be resolved by the storage engine (access method) by directly observing the table or index record. A non-sargable predicate, conversely, requires a higher level of the DBMS to take action. For example, the outcome of WHERE lastname = 'Doe' can be decided by the storage engine by simply looking at the contents of the field lastname of each record. On the other hand, WHERE UPPER(lastname) = 'DOE' requires execution of a function by the SQL engine, which means the storage engine will have to return all rows it reads (provided they match possible other, sargable predicates) back to the SQL engine for evaluation, incurring additional CPU costs.

You can see from the original definition that sargable predicates can apply not only to index scans, but also to table (segment in System R terminology) scans, as long as the conditions "column comparison-operator value" are met and therefore they can be evaluated by the storage engine. This is indeed the case with Db2, a descendant of System R in many ways:

Index sargable predicates are not used to bracket a search, but are evaluated from the index if one is chosen, because the columns involved in the predicate are part of the index key. These predicates are also evaluated by the index manager.

Data sargable predicates are predicates that cannot be evaluated by the index manager, but can be evaluated by Data Management Services (DMS). Typically, these predicates require the access of individual rows from a base table. If necessary, DMS will retrieve the columns needed to evaluate the predicate,

The fact that in SQL Server-speak sargable predicates are only those that can be resolved using index seeks is probably determined by its storage engine's inability to apply such predicates during table scans.

Sargable and non-sargable predicates are sometimes described as "stage 1" and "stage 2" predicates respectively (this also comes from Db2 terminology). Stage 1 predicates can be evaluated at the lowest level of query processing, while reading table or index records. Rows that match stage 1 conditions, if any, are sent to the next level, stage 2, of evaluation.


1 -- Segment in System R is the physical storage of a table's tuples; a segment scan is somewhat equivalent to a table scan in other DBMSes.

2 -- RSI -- RSS3 Interface, a tuple-oriented query interface. The interface function relevant to this discussion is NEXT, which returns the next row matching query predicates.

3 -- RSS, or Research Storage System, the storage subsystem of System R.

  • "directly observing the table or index record" what does that mean? I mean certainly = UPPER() is a function call, but so is memcmp by itself. It would be relatively easy to write a memcmp that assumes ASCII and ignores case (just look at the second nibble). Does that make it SARGABLE? Also see @Ypercube's example, dba.stackexchange.com/questions/162263/… – Evan Carroll Sep 19 at 2:46
  • 4
    @EvanCarroll It means looking at the table or index record directly, without recourse to database functions implemented outside the storage engine (e.g. within the query processor/execution engine/expression service). In ypercube's example, the query is preprocessed by the planner/optimizer such that the non-SARGable search is expressed in SARGable terms. – Paul White Sep 19 at 6:49
  • What does "looking at the table or index record directly" mean? I'm not sure how that's explaining "directly observing the table or index record". Is x=0 SARGable? What about -0 = +0, ' ' = '' or spatial equality? What would be an example of something that was SARGable, for sure? When you say "without recourse to database functions implemented outside the storage engine" you're including in Ypercube's example DATE() which is included inside the storage engine. Why isn't that SARGable by itself? – Evan Carroll Sep 19 at 6:59
  • 2
    @EvanCarroll Take some time to read the referenced paper, and perhaps go through this answer again after that. If you still have questions that would be on-topic here, you could ask them. Note in passing that DATE() is not a real (SQL Server) function, but (I presumed) Mr. Cube's shorthand for a type conversion. We can also discuss this in chat if you like. – Paul White Sep 19 at 8:42

For me, SARGable means that SQL Server can perform an index seek using your search predicates.

You can't just say the DBMS can "take advantage" of an index, because with a non-sargable predicate, SQL Server may end up scanning a nonclustered index.

A Search ARgument ABLE is a predicate is one where

SQL SERVER can to utilize an index seek operation, if an index exists
"Pro SQL Server Internals"

A SARGable predicate is one where SQL server isolates the single value or range of index key values to process such as in the use of operators: =, >, >=, <, <=, IN, BETWEEN, and Like (in the case of prefix matching)

WHERE name like 'SARGable%'
WHERE name like '%non-SARGable%'

Non-SARGable operators such as: NOT, NOT IN,<>, LIKE (not prefix matching), the use of functions or calculations against the table, and type conversions where the datatype does not fulfill the index created.

Example:

DROP TABLE dbo.Testing;
GO

CREATE TABLE Testing (
    WeirdDatatype   int NOT NULL,
    SomethingElse   char(200)
);

CREATE NONCLUSTERED INDEX IDX_ALWAYS_SARGable
    ON dbo.Testing( SomethingElse);

CREATE NONCLUSTERED INDEX IDX_NOT_ALWAYS_SARGable
    ON dbo.Testing(SomethingElse);

INSERT INTO dbo.Testing
        ( WeirdDatatype, SomethingElse )
SELECT TOP 1000 m.message_id, CONVERT(char(200), m.text)
FROM sys.messages AS m;

Now we run:

SELECT *
FROM dbo.Testing AS t
WHERE  t.WeirdDatatype = 1001;
SELECT *
FROM dbo.Testing AS t
WHERE t.SomethingElse LIKE 'Line%'
SELECT *
FROM dbo.Testing AS t
WHERE t.SomethingElse LIKE '%Line%'
     AND t.WeirdDatatype = 1001;

The results are [1]

Lets look at the properties of the SARGable query (Index Seek) enter image description here

The query optimizer is able to define a limit in the index of a start and end. It has a search arguement to query with.

Now the non-SARGable query:

enter image description here

You can see with the beginning of the predicate '%non..%' does not allow the query optimizer to DEFINE a begin and end or range in the index. It must now search the whole table (scan).

  • So again, if an index is later created that supports WHERE name like '%non-SARGable%' does that make the condition sargable? And, if so, aren't we talking about a specific implementation drawback? IE., Shouldn't we say "not sargable as of SQL Server 2016" – Evan Carroll Jan 25 '17 at 22:07
  • Although anything is possibe in releases of SQL Server. While keeping in mind the tipping point of an index a wildcard at the beginning of the predicate would it very difficult for the query optimizer to define a range of values within an index to search for. Thus using a scan and the predicate is then called a non-SARGable predicate. – Vic Work Jan 25 '17 at 23:00
  • 2
    Of course it's implementation specific. WHERE DATE(datetime_column) = '2001-01-01' for example is "sargable" (will do index seek) in newer SQL Server versions (2008+ I think) but not in older ones. – ypercubeᵀᴹ Apr 13 at 8:42

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.