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.

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. — LukLed, Feb 17 '11 at 01:16
@Mitch Wheat: What is wrong with empty string equivalent to NULL? — LukLed, Feb 17 '11 at 01:21
@drachenstern: No. COALESCE is just universal, workin on every database. It does mean nothing in terms of performance. — LukLed, Feb 17 '11 at 01:22
And my advice is to use first version, because second can probably kill index usage on older SQL Server version. — LukLed, Feb 17 '11 at 01:23
@Lukled: NULL is not a value. Null is taken to mean unknown or missing. Empty string means you know it and it's empty. — Mitch Wheat, Feb 17 '11 at 01:25
@Mitch Wheat: Tell this to engineers at Oracle:) In Oracle, empty string equals null. — LukLed, Feb 17 '11 at 01:28
@lukled I still marked the question as Sql-Server to begin with ;) — jcolebrand, Feb 17 '11 at 01:29
@drachenstern: Yes. I gave my answer about your SQL and told, that first one is safer for me. And then I argued about NULL being other value than empty string. — LukLed, Feb 17 '11 at 01:31
@LukLed: COALESCE is demonstratably slower in SQL Server due to datatype handling. — gbn, Feb 17 '11 at 05:15
@gbn: What do you mean by demonstratably? Is it really visible? — LukLed, Feb 17 '11 at 05:22
@LukLed: yes. one example http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx . Even if it's marginal like-for-like, COALESCE can be side-effecting. ISNULL takes datatype of first argument, COALESCE takes datatype with highest precedence. So a `WHERE smallintcol = COALESCE (@smallintvalue, @intvaue)` means a conversion of smallintcol to int... — gbn, Feb 17 '11 at 05:29
@gbn: I copied code from this test and run it on SQL Server 2008. COALESCE was much faster in (string, string) and had the same performance in (null, string). On the other side, in (int, int) ISNULL was faster, but the difference was much lower than (string, string). So I wouldn't say ISNULL is better. It depends. — LukLed, Feb 17 '11 at 05:51
@drachenstern: either place, really. here you'll get more views — gbn, Feb 17 '11 at 06:07
@gbn Yeah, I didn't think this was really ninja level stuff, so wasn't sure :\ — jcolebrand, Feb 17 '11 at 06:08
What are you asking? `IS NOT NULL` or "IS NOT NULL AND IS NOT EMPTY STRING"? — Martin Smith, Sep 11 '15 at 20:20
@MartinSmith "does this field have a value that is not the empty string" — jcolebrand, Sep 11 '15 at 21:47

Martin Smith · Answer 1 · 2015-09-11T22:08:58.260

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.

score 2 · Accepted Answer · answered Feb 17 '11 at 01:25

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.

answered Feb 17 '11 at 01:25

Thomas

63,911
12
95
141

1

Why do you think that SQL-Server is smart enough to translate it to a sargable expression? [As far as i know](http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable) `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. http://connect.microsoft.com/SQLServer/feedback/details/336002/unnecessarily-bad-performance-for-coalesce-subquery – Tim Schmelter Dec 16 '13 at 21:58
1

@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. – Thomas Dec 17 '13 at 15:14
2

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. – Tim Schmelter Dec 17 '13 at 15:56
IsNull was actually given to Microsoft from Sybase. Perhaps its the most confusion function name of any product Microsoft has purchased – John Zabroski Dec 21 '18 at 22:20

score -3 · Answer 3 · edited Sep 11 '15 at 20:14

-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

edited Sep 11 '15 at 20:14

indubitablee

8,136
2
25
49

answered Sep 10 '15 at 08:49

IGNACE Bruno

1

Sargable queries using ISNULL in TSQL

3 Answers3

Linked