I'm looking to prevent non-sargable expressions in my queries, which is the better way to check for a null condition?
AND c.Account IS NOT NULL
AND c.Account <> ''
or
AND ISNULL(c.Account,'') <> ''
It dawned on me to point out that Account
is coming from a LEFT JOIN
so it may be null. I want the cases where they only intersect, which means I should really just use an INNER JOIN
huh? Thanks for the facepalms ;)
However, overlooking that nauseating self realization, I still want to know the answer to this in the general case where I can't make Account
a NOT NULL column.
C.Account <> ''
is equivalent toISNULL( c.Account, '' ) <> ''
SQL Server is probably smart enough to translate IsNull into the equivalent SARG expression but if you are bent on using a function, then Coalesce is a better choice because it is part of the SQL Standard, allows for multiple values (instead of just two with IsNull) and avoids using quite possibly the most confusing function name Microsoft ever devised in IsNull.