Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

Sargable vs Non-sargable

Feb 2 2012 12:00AM by vanne040   

I have always heard about the terms sargable and non-sargable but never really understood how they help in developing efficient sql code. Here is what I have found out...

Try to avoid WHERE clauses that are non-sargable. The term “sargable” (which is in effect a made-up word) comes from the pseudo-acronym “SARG”, which stands for “Search ARGument,” which refers to a WHERE clause that compares a column to a constant value. If a WHERE clause is sargable, this means that it can take advantage of a useful index (assuming one is available) to speed completion of the query. If a WHERE clause is non-sargable, this means that the WHERE clause (or at least part of it) cannot take advantage of an index, instead performing a table/index scan, which may cause the query’s performance to suffer.

Non-sargable search arguments in the WHERE clause, such as “IS NULL”, “<>”, “!=”, “!>”, “!<”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%500'” generally prevents (but not always) the query optimizer from using a useful index to perform a search. In addition, expressions that include a function on a column, expressions that have the same column on both sides of the operator, or comparisons against a column (not a constant), are not sargable.

But not every WHERE clause that has a non-sargable expression in it is doomed to a table/index scan. If the WHERE clause includes both sargable and non-sargable clauses, then at least the sargable clauses can use a useful index (if one exists) to help access the data quickly.

Read More..   [32134 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


vanne040
86 · 2% · 657
20
 
6
 
11
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

10  Comments  

  • NIce article

    commented on Feb 5 2012 10:34PM
    harishs
    288 · 0% · 150
  • I don't think it's only relative to comparisons involving a constant.

    For instance, I call such a clause NON SARGABLE as well: SELECT [...] WHERE DATEPART(YYYY, Table1.MyDate) = Table2.Year -- Where MyDate is indexed

    But this one is (still according to me): SELECT [...] WHERE Table1.MyDate BETWEEN DATEADD(YYYY, Table2.Year - 1900, 0) AND DATEADD(YYYY, Table2.Year - 1899, 0)

    commented on Feb 6 2012 2:22AM
    Sergejack
    41 · 4% · 1395
  • Using non-sargable is a performance killer - and can be a reason why an optimizer will use a Clustered Index Scan . Why does Query Optimizer choose a Clustered Index Scan?

    commented on Jun 30 2012 3:29AM
    Jack Vamvas
    5 · 26% · 8528
  • But not every WHERE clause that has a non-sargable expression in it is doomed to a table/index scan. If the WHERE clause includes both sargable and non-sargable clauses, then at least the sargable clauses can use a useful index (if one exists) to help access the data quickly.

    You need to qualify this statement. While it may be true for predicates combined using the AND operator, it's incorrect for those combined with OR ie:

        WHERE [SARGABLE PREDICATE] AND [NON-SARGABLE PREDICATE]   -- MAY result in a seek
    

    while

        WHERE [SARGABLE PREDICATE] OR [NON-SARGABLE PREDICATE]   -- ALWAYS results in a scan
    

    Take a look at the plans for Query 3 and Query 4 when you execute the following:

        SELECT name INTO #SARG FROM master.sys.columns;
        CREATE CLUSTERED INDEX ixSARGname ON #SARG(name);
    
        -- Query 3: results in index seek
        SELECT * FROM #SARG
        WHERE 
            name LIKE 'ACTIONID%'         -- sargable predicate
            AND name LIKE '%ACTIONID';    -- non-sargable predicate
    
        -- Query 4: results in index scan as OR operator used to combine predicates
        SELECT * FROM #SARG
        WHERE 
            name LIKE 'ACTIONID%'       -- sargable predicate
            OR name LIKE '%ACTIONID';   -- non-sargable predicate
    
        DROP TABLE #SARG;
    
    commented on Jul 2 2012 5:25AM
    a.diniz
    316 · 0% · 139
  • Check this out as well "What does Avoid non SARGable where clause mean?" - http://vadivel.blogspot.in/2011/11/what-does-avoid-non-sargable-where.html

    commented on Jul 4 2012 8:24PM
    Vadivel
    489 · 0% · 79
  • Nice blog Vadivel.

    I'm not sure that Example 4:

    SELECT COUNT(*) FROM tblSARGTest WHERE ProductID = ProductID;

    is the best illustration of the same column appearing on both sides of the predicate. It is a tautology (will always be true) so is logically equivalent to there being no predicate at all:

    SELECT COUNT(*) FROM tblSARGTest;

    which would result in a table scan. While the optimizer doesn't recognise the tautology, the example doesn't demonstrate this and may leave some readers wondering. Perhaps include the Clustered Index Scan tooltip in the screenshot and highlight the 'Predicate' section of the index scan operator to illustrate that the plan really is scanning for rows where PredicateID equals PredicateID. Just a suggestion.

    You may think I'm knit-picking (well, I am a bit) :-) but consider the example of the absurdity (will never be true):

    SELECT COUNT(*) FROM tblSARGTest WHERE ProductID <> ProductID OPTION(RECOMPILE);

    This results in a constant scan (!) and neither the table or its stats objects are referenced at all! This is because, early in the optimization process, the optimizer performs 'contradiction detection' and removes sections of the query which have been written in a way that will always return no rows (for any and all data in the database). Check the execution plan if you don't believe me.

    This brings me on to a question I have on Example 5, Implicit Type Conversion...

    I understand and expect the results you present in the example, but note that implicit conversion doesn't occur in the following case (assume the table is populated):

    CREATE TABLE strange (n tinyint identity primary key); -- using a single byte, n has a mximum value of 255

    SELECT n FROM strange WHERE n = 256;

    which results in an index seek! Why is this SARGable? The predicate n = 256 compares a tinyint with a constant of higher type-precedence than tinyint yet no implicit conversion is performed. Is due to the way the tinyint and int are stored? If so, why isn't there an optimization for this? In the case of a SARGable expression, the index seek is probably efficient enough but what about non-SARGable expressions? Why perform an index scan when the expression you're looking for won't be found as it's too big to fit in the index key?

    commented on Jul 5 2012 5:16AM
    a.diniz
    316 · 0% · 139
  • SELECT COUNT(*) FROM tblSARGTest WHERE ProductID = ProductID;

    is actually equivallent to

    SELECT COUNT(*) FROM tblSARGTest WHERE ProductID IS NOT NULL

    commented on Jul 5 2012 5:58AM
    Sergejack
    41 · 4% · 1395
  • In the case of a SARGable expression, the index seek is probably efficient enough but what about non-SARGable expressions? Why perform an index scan when the expression you're looking for won't be found as it's too big to fit in the index key?

    If you're thinking "if the optimizer was able to determine that the expression was too big to fit in the index key, the expression would be SARGable not non-SARGable", what about scans caused by pattern matching predicates such as:

    someCol LIKE '%some pattern'

    for example? '%some pattern' is non-SARGable but if someCol had type varchar(3), the predicate would never be true yet an index scan would be performed all the same. Seems like an aweful waste!

    commented on Jul 5 2012 6:02AM
    a.diniz
    316 · 0% · 139
  • SELECT COUNT(*) FROM tblSARGTest WHERE ProductID = ProductID;

    is actually equivallent to

    SELECT COUNT(*) FROM tblSARGTest WHERE ProductID IS NOT NULL

    Interestingly, while it generates the same plan, the row size of the Index Scan operators differ. Also, why is the scan over the nonclustered index rather than the clustered one?

    commented on Jul 5 2012 6:33AM
    a.diniz
    316 · 0% · 139
  • While the operators used to be a big deal in SQL Server 7, the relevance and optimization of the query optimizer is not longer closely established.

    For example, it used to be faster to create a join table when multiple arguments were desired for an attribute rather than using OR or IN. ex. Select ... From SomeTable where SomeValue in (1,2,3). Historically, using this example the query optimizer would make three passes through the table or index. Since SQL Server 2000, this is no longer true. The query optimizer builds an effective query plan.

    !=, <> and others also are less relevant.

    Something that appears to be missed is the manipulation of the data being compared. That is definitely something that the query optimizer cannot tune.

    For example,

    -- Get a list of all records from SomeTable that were produced on the 5th day of the month.

    SELECT ... FROM SomeTable WHERE DATEPART(day, RecordDateTime) = 5

    This example is easy to see how the query optimizer must scan an entire index (clustered or non) extract the day, and then compare it to the literal 5.

    These are the wordt kind of queries that are not SARGable.

    commented on Jul 5 2012 9:48AM
    benstaylor
    436 · 0% · 92

Your Comment


Sign Up or Login to post a comment.

"Sargable vs Non-sargable" rated 5 out of 5 by 20 readers
Sargable vs Non-sargable , 5.0 out of 5 based on 20 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]