Would it matter, performance wise using

var1 IS NULL

instead of

var1 = NULL

?

Performance-wise it wouldn't make a difference.

However, to get a TRUE/FALSE result from a where clause like "WHERE var1 = NULL" or a statement like "IF var1 = NULL", you would need to SET ANSI_NULLS OFF.

This is not recommended, since you could end up turning ANSI_NULLS OFF in some situations, and ON in other situations; it will make your queries/applications non-portable; and it can cause you to return unexpected/incorrect results.

I highly recommend leaving SET ANSI_NULLS ON and using ANSI Standard NULLs for these reasons.

Here is the question I asked Michael

> Dear Michael,
>
> I just finished reading your article on SQL NULLs and it was a
> great break
> down on how NULLS actually function.
>
> My question deals with the last rule.
>
> Rule #4: The ANSI Standard COALESCE() and CASE syntaxes are
> preferred over
> ISNULL() or other proprietary syntax
>
> I have always used ISNULL() for replacing my NULL values. Can you
> providesome insight into why COALESCE() is the preferred? Is it
> because ISNULL is
> a Microsoft function only or does it provide some other benefit.
>
> Have a good day.
>
> Peace.
>
> John Dobson

Here is the response so that everyone can see this post.  Peace.

Hi John,

Excellent question!  If you have a chance, you might want to post it to the discussion board so we can share the answer with others - I'm sure plenty of people have the same or similar questions!

COALESCE() is preferred because it is ANSI Standard.  That means you can use COALESCE() on SQL Server, Oracle, or any other ANSI SQL-92 Standards compliant RDBMS.  The syntax will always be the same on any platform, and the results will be consistent across platforms.

ISNULL() is specific to the MS SQL Server platform, which means that it is not necessarily implemented on other RDBMS systems; and even if it is implemented on other RDBMS systems, it might not have the same syntax or return the same result as the Microsoft version.  This even applies to future versions of the same product; i.e., SQL 2005 isn't guaranteed to have the same syntax as SQL 2000 when dealing with the non-ANSI Standard platform-specific functions.  That's not to say that MS will change the syntax or results of the ISNULL() function, or drop it completely from usage; but there is always a small chance that something like this could happen in a future version of SQL Server.

These are the reasons I recommend using ANSI Standard functionality whenever possible.

Thanks,

Michael Coles, MCDBA

Hello,

This is a very good article. One has to read it once in a while. I am well familiar with the concept and these 4 rules. But  every now and then, especially in the ad hoc outer join queries worked on with a non-SQL speaking visitor I start automatically typing translating from his English to SQL "...where my column = NULL... oops...IS NULL"

As for the stored code, it definitely should be as much language-independent as possibe. Listen to Miichael, use ANSI syntax.

Yelena

Excellent article, Michael.

There are a couple of other trade-offs to consider when deciding whether to use COALESCE() or ISNULL(). I'll leave it to you to decide which are the pro's and con's of each

In the following repro script:

CREATE TABLE dbo.MyTable(
  i int NOT NULL PRIMARY KEY,
  j int NULL
)
GO

INSERT INTO dbo.MyTable(i, j)
SELECT 1, 100 UNION
SELECT 2, 200 UNION
SELECT 3, NULL
GO

SELECT COALESCE(j, 'No Name Given')
FROM dbo.MyTable

SELECT ISNULL(j, 'No Name Given')
FROM dbo.MyTable
GO

DROP TABLE dbo.MyTable

We see that COALESCE() has weaker type checking than ISNULL, as it allows the selection of the 'j' column where the value is non-NULL. ISNULL(), on the other hand, rejects the statement from the outset. Score one for ISNULL(), in my opinion.

But COALESCE() allows us to do this:

DECLARE @MyVar1 int, @MyVar2 int, @MyVar3 int
SET @MyVar1 = NULL
SET @MyVar2 = NULL
SET @MyVar3 = 3
SELECT COALESCE(@MyVar1, @MyVar2, @MyVar3)

So COALESCE() allows "n" number of expressions to be listed. Score one for COALESCE().

This last one will getcha for sure if you're not careful:

DECLARE @MyVC1 varchar(2), @MyVC2 varchar(4)
SET @MyVC1 = NULL
SET @MyVC2 = 'abcd'

SELECT COALESCE(@MyVC1, @MyVC2)
SELECT ISNULL(@MyVC1, @MyVC2)

This seems to be related to the "weaker" type-checking that I pointed out earlier. COALESCE() is a bit looser with the implicit conversions, where ISNULL() will always convert to the datatype of the first argument. In this case, COALESCE() seems to be the better choice (trust me, I spent a half hour helping a developer track down what was going on with an ISNULL() construct once), but it may be only masking some sloppy coding which would come back to bite you at a later time.

HTH,

TroyK

[Quote]

We see that COALESCE() has weaker type checking than ISNULL, as it allows the selection of the 'j' column where the value is non-NULL. ISNULL(), on the other hand, rejects the statement from the outset. Score one for ISNULL(), in my opinion.

But COALESCE() allows us to do this:

DECLARE @MyVar1 int, @MyVar2 int, @MyVar3 int
SET @MyVar1 = NULL
SET @MyVar2 = NULL
SET @MyVar3 = 3
SELECT COALESCE(@MyVar1, @MyVar2, @MyVar3)

So COALESCE() allows "n" number of expressions to be listed. Score one for COALESCE().

[/End Quote]

__________________________________________________________-

The above can be a little misleading.  Even though you can list "n" number of expressions, only the first non-NULL will be displayed.  Just wanted to point that out so noone will try to stack several columns together and thinking this will handle all the NULL values.

Peace.

Thanks for the feedback!  Excellent points.  I've also read, but haven't yet had a chance to verify, that ISNULL() performs better in some specific situations in WHERE clauses.  That might be a consideration as well.

Thanks again.