Sargable queries using ISNULL in TSQL

Question

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.

Answer 1

Just use WHERE c.Account <> ''

This doesn't evaluate to true for either null or empty string and can potentially be evaluated using a range seek on Account.

Use of either ISNULL or COALESCE would make the expression unsargable and is not needed anyway.

If you want to distinguish truly empty strings from strings consisting entirely of spaces you could use

 WHERE c.Account IS NOT NULL AND DATALENGTH(c.Account) > 0

Which combines one sargable predicate that allows an index seek to skip the nulls in an index with an unsargable one against the remaining values.