Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SARGable Expand / Collapse
Author
Message
Posted Friday, February 26, 2010 12:44 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:25 AM
Points: 1,567, Visits: 2,628
...I too got it right..
Am sure, it's a just a typo..
Post #873223
Posted Friday, February 26, 2010 2:00 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
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.
Post #873251
Posted Friday, February 26, 2010 2:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 4:32 PM
Points: 1,341, Visits: 556
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
Post #873272
Posted Friday, February 26, 2010 3:44 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:17 AM
Points: 1,608, 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.
    Post #873303
    Posted Friday, February 26, 2010 6:38 AM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Tuesday, September 30, 2014 7:42 AM
    Points: 3,688, Visits: 72,435
    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
    Post #873377
    Posted Friday, February 26, 2010 6:42 AM
    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Friday, May 9, 2014 12:47 AM
    Points: 3,448, Visits: 4,407
    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'
    Post #873381
    Posted Friday, February 26, 2010 6:46 AM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Friday, July 22, 2011 4:13 AM
    Points: 1,149, 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).
    Post #873384
    Posted Friday, February 26, 2010 6:47 AM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Tuesday, September 30, 2014 4:32 PM
    Points: 1,341, Visits: 556
    no, no, no, i should not have to guess or assume what the question 'should' say.

    Martin
    Post #873385
    Posted Friday, February 26, 2010 7:57 AM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Friday, November 21, 2014 4:43 PM
    Points: 1,357, Visits: 1,726
    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.)
    Post #873447
    Posted Friday, February 26, 2010 8:29 AM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: Administrators
    Last Login: Yesterday @ 8:25 PM
    Points: 31,279, Visits: 15,740
    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
    Post #873480
    « Prev Topic | Next Topic »

    Add to briefcase ««123»»

    Permissions Expand / Collapse