SARGable

  • agrawal.prakriti

    Mr or Mrs. 500

    Points: 572

    Comments posted to this topic are about the item SARGable

  • WayneS

    SSC Guru

    Points: 95392

    The "correct" answer doesn't have a column in the where clause... how can that be SARGable?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • SQLRNNR

    SSC Guru

    Points: 281252

    WayneS (2/25/2010)


    The "correct" answer doesn't have a column in the where clause... how can that be SARGable?

    I would have to agree here. The lack of a column in the where clause makes none of the answers correct.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • dduncan

    Right there with Babe

    Points: 772

    WayneS (2/25/2010)


    The "correct" answer doesn't have a column in the where clause... how can that be SARGable?

    Agreed. If the query is not RUNable then it can't be SARGable. 😀

  • Arjun SreeVastsva

    SSCertifiable

    Points: 7135

    Agreed. ..The Answers was not properly printed wrong

    A condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query (using index seeks, not covering indexes). The term is derived from a contraction of Search Argument.

  • Fal

    SSCrazy

    Points: 2984

    Hmmm. Either assume the pseudo code has a typo and choose the "right" answer, or take the question literally and choose the "wrong" answer. A 50/50 chance. I'm gonna have to start using dice to answer the QotD. 🙂

    S.

  • Krishnraj

    SSCrazy

    Points: 2026

    Yeah, I agree with CirquedeSQLeil, The lack of a column in the where clause makes none of the answers correct

  • SandyDB

    Grasshopper

    Points: 15

    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 an index to perform a search.

    How can where clause without column name can be SARGable while index can not be performed on this?

    Please correct me in case I am wrong.

  • Kingston Dhasian

    SSCoach

    Points: 19794

    I noticed it only now. The first option has a column in the Where Clause. It must be a typo for sure. Anyways i got it right:-)


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • LythamsFinest

    SSCommitted

    Points: 1786

    SandyDB (2/26/2010)


    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 an index to perform a search.

    How can where clause without column name can be SARGable while index can not be performed on this?

    Please correct me in case I am wrong.

    The content of your reply looks remarkably like paragraph 2 of http://www.sql-server-performance.com/tips/t_sql_where_p2.aspx) .. so I'm not gonna argue with Brad MacGehee 😉

  • Joy Smith San

    SSC-Insane

    Points: 24877

    ...I too got it right..

    Am sure, it's a just a typo..

  • Tao Klerks

    SSCarpal Tunnel

    Points: 4297

    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

    SSCommitted

    Points: 1746

    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

    SSCrazy

    Points: 2288

    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

    SSC-Insane

    Points: 23099

    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[/url]
    For tips on how to post your problems[/url]

  • Viewing 15 posts - 1 through 15 (of 30 total)

    You must be logged in to reply to this topic. Login to reply