Post reply

Concat columns in Where clause

  • November 13, 2013 at 11:04 am

    #1666159

    I'm not saying either of the solutions is right or wrong. Both are just different in terms of the final result. They're just not equivalent.

    Whether the current approach works just because of plain luck (or the current data distribution) or if it's really intended needs to get clarified.

    But for the taks itself (taking aside the concatenation) your preferred solution is NOT EXISTS?

    Is this "usually" the fastest solution for a large table against a small lookup table or is this your preferred method to start with?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • November 13, 2013 at 11:42 am

    #1666172

    LutzM (11/13/2013)

    But for the taks itself (taking aside the concatenation) your preferred solution is NOT EXISTS?

    Is this "usually" the fastest solution for a large table against a small lookup table or is this your preferred method to start with?

    On non-nullable columns, NOT IN and NOT EXISTS are usually equivalent. When the columns are nullable, NOT IN performs terribly and can produce different results. EXISTS makes it easier to compare multiple columns without making the predicate non-SARGable

    I also find EXISTS easier to read, but that's a personal thing.

    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 - 16 through 16 (of 16 total)

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