SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SARGable


SARGable

Author
Message
Oleg Netchaev
Oleg Netchaev
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3082 Visits: 1910
Tao Klerks (2/26/2010)
Hmm, I was more bothered by the fact that NOT IN() is assumed to NOT be SARGable... In SQL 2000 this was always true when there was more than one value in the NOT IN() parentheses, but as of SQL 2005 the optimizer does a pretty good job of using indexes even then!


At the place where I work we have a weekly Lunch and Learn sessions. About 6 months ago I was giving a presentation about indexes which included a dissection of the SARGable predicates based on the original article by Brad McGehee (the one which is included as a hyperlink in this QoD answer section). Well, when the NOT IN () was investigated when appied to one one the tables in one of our SQL Server 2005 databases, I could not demonstrate that NOT IN () is not SARGable because the execution plan had shown that the respective index was still used. Fortunately, I still had the old SQL Server 2000 database on one of my boxes at home, so I could later confirm the validity of the NOT IN() is not SARGable argument.

On the unrelated note, it looks like occasionally it pays off to come to work late, after Mr. Jones already edited the QoD :-). Since I am very familiar with Brad McGehee's article on SARGable predicates, I did not have any problems answering th QoD. Original typo aside, this is a very good question. Thank you Agrawal.

Oleg
john.arnott
john.arnott
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7086 Visits: 3059
Aside from the typo, this was a good one. It introduces the concept simply and provides a good resource for further understanding. Although I'd learned long ago that one should avoid functions in the WHERE clause, as with: Where left(column, 1) = 'R', it's good to see how other constructions will or will not fall into the SARGable category.
KevinC.
KevinC.
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1081 Visits: 504
I misremembered what SARGable meant and so I got lucky in my answer. A good reminder question.

--
Kevin C.
Tom Thomson
Tom Thomson
SSC Guru
SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

Group: General Forum Members
Points: 50796 Visits: 13159
john.arnott (2/26/2010)
Aside from the typo, this was a good one. It introduces the concept simply and provides a good resource for further understanding. Although I'd learned long ago that one should avoid functions in the WHERE clause, as with: Where left(column, 1) = 'R', it's good to see how other constructions will or will not fall into the SARGable category.

In my view the use of LEFT should not prevent an index seek and that it does is a bug in the optimiser; after all, col LIKE 'this%' is sargable, so the logically identical LEFT(col,4) = 'this' should also be sargable: having to know which way to write a particular predicate to get the optimiser to notice that an index can be used is really annoying (and a waste of space in every developer's or dba's memory). The current situation is no less stupid than it would be if col < 3 were sargable but 3 > col were not.

I agree it was a fair query - but I looked late and never saw the typo.

Tom

john.arnott
john.arnott
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7086 Visits: 3059
Tom.Thomson (2/28/2010)
john.arnott (2/26/2010)
Aside from the typo, this was a good one. It introduces the concept simply and provides a good resource for further understanding. Although I'd learned long ago that one should avoid functions in the WHERE clause, as with: Where left(column, 1) = 'R', it's good to see how other constructions will or will not fall into the SARGable category.

In my view the use of LEFT should not prevent an index seek and that it does is a bug in the optimiser; after all, col LIKE 'this%' is sargable, so the logically identical LEFT(col,4) = 'this' should also be sargable: having to know which way to write a particular predicate to get the optimiser to notice that an index can be used is really annoying (and a waste of space in every developer's or dba's memory). The current situation is no less stupid than it would be if col < 3 were sargable but 3 > col were not.

I agree it was a fair query - but I looked late and never saw the typo.
Tom, I would tend to agree with your disappointment in the optimizer not recognizing the LEFT(col,4) ='xxxx' construction as equivalent to col LIKE 'xxxx%'. For what it's worth, I just confirmed on a SQL 2005 test db I already had that the estimated plan for a LIKE specifies an index seek where a logically equivalent LEFT() resolves to an index scan. If/Until MS decides to address this, I suppose we'd better continue to pay attention to our predicate constructions at a fairly low level.

------
edit to clarify I'm on SQL 2005.
Dietmar Weickert
Dietmar Weickert
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1782 Visits: 374
Steve Jones - Editor (2/26/2010)
The answers have been edited to include the column and "none of the above" has been changed. I will award back points. My apologies as I didn't notice the missing columns.

Hi Steve, I did not get back my points so far...

Best regards,
Dietmar Weickert.
Paul White
Paul White
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79968 Visits: 11400
Dietmar Weickert (2/26/2010)
Once again a crap QotD.

  • A missing column name a typo? Hard to believe.

  • No info about indices on the table/view to be queried? Who could potentially know about SARGable operators. And even if there were this information, who is able to predict the optimizers way to treat a query.


  • Just choose whatever you want. It may be right or wrong anyways. Leaves a QotD without value for the community.

    Anyone else bugged by a persistent whining noise? Laugh



    Paul White
    SQLPerformance.com
    SQLblog.com
    @SQL_Kiwi
    Paul White
    Paul White
    SSC Guru
    SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

    Group: General Forum Members
    Points: 79968 Visits: 11400
    Bit hash calling the lack of an automatic transformation from LEFT to LIKE a bug!
    Are you sure it is a provably safe transformation in absolutely all cases?



    Paul White
    SQLPerformance.com
    SQLblog.com
    @SQL_Kiwi
    Igor Micev
    Igor Micev
    SSCoach
    SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

    Group: General Forum Members
    Points: 19758 Visits: 5191
    Oh, I adore questions with fast retrieval of rows. Thank you

    Igor Micev,
    My blog: www.igormicev.com
    Tom Thomson
    Tom Thomson
    SSC Guru
    SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)SSC Guru (50K reputation)

    Group: General Forum Members
    Points: 50796 Visits: 13159
    SQL Kiwi (3/30/2010)
    Bit hash calling the lack of an automatic transformation from LEFT to LIKE a bug!
    Are you sure it is a provably safe transformation in absolutely all cases?

    I thought I'd replied long ago, but noticing a new post here I saw I hadn't. better late than never, I suppose.

    The optimiser would have to look at each case and decide whether the transformation was allowable; and if it was allowable, is it worth doing in the particular case (the latter is the same as for any other choice the optimiser makes, of course).

    The transformation is from LEFT(Col,L)=S to Col LIKE tr(left(S,L))+'%', where the expression tr(left(S,L))+'%' is calculated once only (by the optimiser); tr is the string escaping needed to deal with wildcards occurring within left(S,L) - they must not be treated as wildcards because they represent themselves; the transformation is permissable provided that (i) left(S,L) does not have a space as its final character and (ii) S and L are constants known to the optimiser, and will be the same each time the generated plan is used. If any of these conditions fails, the optimiser must not male the transformation.

    Of course there are many other "bugs" that probably have higher priority than this ("bug" of course means "possibility for improvement in teh optimiser", not anything else).

    Tom

    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum








































































































































































    SQLServerCentral


    Search