Sargable queries using ISNULL in TSQL

问题:

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.

回答1:

C.Account <> '' is equivalent to ISNULL( 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.

回答2:

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.

回答3:

COALESCE IS SARGABLE as it's only a shortcut expression (equivalent of using CASE WHEN IS NOT NULL THEN ELSE...).

ISNULL is a built in function so ISNULL is NOT SARGABLE