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


SARGable


SARGable

Author
Message
Joy Smith San
Joy Smith San
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6194 Visits: 3200
...I too got it right..
Am sure, it's a just a typo..
Tao Klerks
Tao Klerks
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1935 Visits: 1249
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!

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
Martin_Burton
Martin_Burton
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1387 Visits: 602
So my answer was wrong according to what the question should have said. In that case my answer was a typo, can i have the point please.

Martin
Dietmar Weickert
Dietmar Weickert
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1672 Visits: 374
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.

    Best regards,
    Dietmar Weickert.
    mtassin
    mtassin
    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: 7108 Visits: 72521
    I have to agree with the complainers here.

    When one of the answers is none of the above, how do I know the abscence of a column name is accidental or intentional?

    I could have picked the one with LIKE 'A%', but the first thing I thought was... you can't do that... so it can't be SARGable and the None of the above is there.

    If I had picked it, I'd just as likely have hit a "None of the above, because WHERE LIKE 'A%' is invalid SQL syntax and is thus non-SARGable'



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link
    For tips on how to post your problems
    vk-kirov
    vk-kirov
    SSCarpal Tunnel
    SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

    Group: General Forum Members
    Points: 4304 Visits: 4408
    I think the typo is quite obvious.
    Thanks to the author: the question was very useful to me, due to the question I learned the new word 'sargable' :-)
    Seth Lynch
    Seth Lynch
    UDP Broadcaster
    UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

    Group: General Forum Members
    Points: 1463 Visits: 603
    I got this right because I read the question quickly and didn't notice the missing columns.

    However, I have gotten these questions wrong before where there were deliberate 'mistakes' like this.

    If this question stood on it's own then it is just a typo. Some questions rely on you spotting or not spotting invalid SQL so the assumption of a typo is dangerous (if losing a point is considered a danger).
    Martin_Burton
    Martin_Burton
    Ten Centuries
    Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

    Group: General Forum Members
    Points: 1387 Visits: 602
    no, no, no, i should not have to guess or assume what the question 'should' say.
    :-)
    Martin
    sknox
    sknox
    Hall of Fame
    Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

    Group: General Forum Members
    Points: 3942 Visits: 2923
    Dietmar Weickert (2/26/2010)

  • 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.



  • To answer the first question, a SARGable operation is not necessarily one that is working on an indexed column. A SARGable operation is one that would provide a better result if the column were indexed. So you don't need to know if there is an index to know it's a SARGable operation.

    To answer the second question, I'd say anyone whose spent a few hours researching performance tuning in SQL server would have a pretty good idea. That's the whole point of SARGable operations -- they've been identified by people who know how the optimizer works as ones which generally enable the query optimizer to use an index to improve performance. You identify them first, and then you know where indexes would help (or not, as the case may be.)
    Steve Jones
    Steve Jones
    SSC Guru
    SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)

    Group: Administrators
    Points: 141882 Visits: 19420
    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.

    Follow me on Twitter: @way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com
    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