Functions as predicates and SARGable queries

  • Very good question.

    Thanks!

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

  • 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

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

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

  • Nice question and explanation, Uwe. Thank you.

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

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

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

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

  • Good Question 🙂

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

  • 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

  • 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