Functions as predicates and SARGable queries

  • SqlMel

    SSCrazy

    Points: 2891

    Very good question.

    Thanks!

    ---------------
    Mel. 😎

  • SQLRNNR

    SSC Guru

    Points: 281210

    Nice question.

    I like how it underscores the "It depends" with SArg-ability and why in the forums it really is good to have both DDL and execution plans when trying to help performance tune a query.

    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

  • Sean Lange

    SSC Guru

    Points: 286423

    Great question. Thanks!!!

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • david.gugg

    SSCertifiable

    Points: 5624

    Nice question, I learned something. I assumed the ISNULL function would make it non-Sargable.


    [font="Tahoma"]Personal blog relating fishing to database administration:[/font]

    [font="Comic Sans MS"]https://davegugg.wordpress.com[/url]/[/font]

  • stephen.long.1

    SSCrazy

    Points: 2568

    Nice question and explanation, Uwe. Thank you.

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Thank you for the post, very very interesting one, never new the word "SARGable" really exists, learnt a good amount of new stuff today. thank you.:-)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • SqlMel

    SSCrazy

    Points: 2891

    SQLRNNR (8/26/2014)


    Nice question.

    I like how it underscores the "It depends" with SArg-ability and why in the forums it really is good to have both DDL and execution plans when trying to help performance tune a query.

    +1

    ---------------
    Mel. 😎

  • Guy Stephens

    Old Hand

    Points: 306

    I expected the optimiser to use the index on code on the basis that the expression was sargible, however I did not expect a SEEK. I was expecting a SCAN because the expression was WHERE [highlight=#ffff11]!=[/highlight] rather than =. Didn't realise the optimiser would convert != into a SEEK < and SEEK >.

    Interesting. Thanks for the question.

  • Hany Helmy

    SSChampion

    Points: 13321

    Ok, that was new for me, never encountered a case like this before, so definitly learnt somthing new today, thx.

  • mbahnu9977

    SSC Rookie

    Points: 37

    Good Question 🙂

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    I am torn between two opinions on this question.

    It is a good question because of the educational value on how the optimizer works and how indexes are (can be) used.

    But it is also dangerous - in my experience, the optimizer can be very hard to predict, and things like this can change between versions, or even depending on the weather. This case is pretty obvious and safe, but as a matter of principle I would personally not submit such a question.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • TomThomson

    SSC Guru

    Points: 104767

    Hugo Kornelis (8/27/2014)


    I am torn between two opinions on this question.

    It is a good question because of the educational value on how the optimizer works and how indexes are (can be) used.

    But it is also dangerous - in my experience, the optimizer can be very hard to predict, and things like this can change between versions, or even depending on the weather. This case is pretty obvious and safe, but as a matter of principle I would personally not submit such a question.

    I can see your point, but I think it's reasonable tosubmit the question. People will learn something, and if they don't realise it may change from one release to the next thy should perhaps learn that from other questions. It would of course be nice for some questions if the explanation explained that things might change, but change for this one is so unlikely that I don't think it should in this case.

    Tom

  • DamianC

    SSCertifiable

    Points: 7770

    good explaination

    - Damian

Viewing 13 posts - 16 through 28 (of 28 total)

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