Post reply

LEFT function at where clause criteria

  • April 11, 2015 at 9:57 am

    #317389

    Hi Everyone,

    Its not a good idea to use functions at where clause, instead of seek, query plan will choose index scan..

    Ex:

    SELECT EmailAddress

    FROM person.contact

    WHERE left(EmailAddress,2) = 'As'

    Sub-tree cost : 20.8989

    I have changed the query to below

    SELECT EmailAddress

    FROM person.contact

    WHERE EmailAddress like 'As%'

    Subtree cost: 4.89000

    sub-tree cost is 0.0032 if i use below query

    SELECT EmailAddress

    FROM person.contact

    WHERE EmailAddress = 'As' ...

    I know i can't use this query either i have to use left function or like keyword to searh email address..is there any we can reduce sub tree cost of query ?...!Thanks for your help..

  • April 13, 2015 at 2:55 am

    #1790447

    If you need everything that starts with As, then you can't use an equality, the LIKE is probably the best.

    Btw, don't look at the subtree cost. Costs are pretty much meaningless values, look at the execution statistics instead, the duration, the CPU, the reads. Those tell you how a query is behaving.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

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