Home Forums SQL Server 2008 T-SQL (SS2K8) comparison in the merge statement about null values RE: comparison in the merge statement about null values

  • Edit: Damned quote bug...

    sqlfriends (8/22/2012)


    Evil Kraig F (8/22/2012)


    sqlfriends (8/22/2012)


    can I use isnull(student.mailingaddress,'')<>isnull(esis.mailingaddress,'')

    Yep, that's the usual workaround, just realize the entire query will be non-SARGable.

    I would like my query to be sargable.

    So what other good approach to do the comparison but still keep null compare valid?

    Thanks.

    I should rephrase that statement. The query MAY be sargeable, but none of these compares will be. They'll always end up as post-lookup predicates (there's an official name out there for that which I don't know). There's no good solution, really, when dealing with NULLS like this. Either you build out the where clause of doom, or you use coding shortcuts that hopefully won't hurt too badly. Avoid using this on joins like the plague, but after a certain point you just go with it.

    I've got a query that uses about 70 of those in a reporting environment right now. It SARGs on the non-compare fields (and indexes I've built specifically for that) and then the compare happens after the rows are memory-joined. It'll seek when appropriate, but you'll have to dig into the query and make sure everything outside of the compare is lined up.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA