7

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.

Improve this question
20
  • 2
    It is better to set Account field as NOT NULL, since these values mean the same to you and it is better to use COALESCE, because it is part of SQL standard. Commented Feb 17, 2011 at 1:16
  • 2
    empty string equivalent to NULL ? Eeeeeek! Commented Feb 17, 2011 at 1:18
  • @lukled ~ So it would be better to use COALESCE where? Commented Feb 17, 2011 at 1:18
  • 1
    @Lukled: NULL is not a value. Null is taken to mean unknown or missing. Empty string means you know it and it's empty. Commented Feb 17, 2011 at 1:25
  • 3
    @Mitch Wheat: Tell this to engineers at Oracle:) In Oracle, empty string equals null. Commented Feb 17, 2011 at 1:28

3 Answers 3

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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.

Improve this answer

4 Comments

Why do you think that SQL-Server is smart enough to translate it to a sargable expression? As far as i know ISNULL/COALESCE are non-sargable and should be replaced with IS NULL OR <> ''. Also, COALESCE has still a serious bug in sql-server so ISNULL should be preferred unti you need COLESCE. It is translated to a CASE which evaluates twice. connect.microsoft.com/SQLServer/feedback/details/336002/…
@TimSchmelter - RE: IsNull, IMO, one should never use IsNull. There is zero excuse. Beyond the fact that it is the single most horribly named function ever devised, it isn't ANSI compliant and the circumstance you noted is from five years ago and only applies when using a subquery (so it may be fixed). Better to write it out as a case statement than to use IsNull. I wish that MS would deprecated it entirely. As to whether it is sargable, I said that MS might be smart enough to do that since it converts the function into a case expression but the odds are that the parser can't handle that.
the bug-status is still active. I'm still on SQL-Server 2005 and at least there it isn't fixes. This query returns two error-messages instead of one(as with ISNULL): SELECT COALESCE(xyz, 0) FROM sys.objects. Most people aren't aware of it but everybody recommends to use COALESCE instead of ISNULL. Even without sub-queries ISNULL takes 20-30% less CPU to run.
IsNull was actually given to Microsoft from Sybase. Perhaps its the most confusion function name of any product Microsoft has purchased
-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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.