Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Four Rules for NULLs


Four Rules for NULLs

Author
Message
Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1168
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls.asp
Vishal Gamji-215308
Vishal Gamji-215308
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 57

Would it matter, performance wise using

var1 IS NULL

instead of

var1 = NULL

?


Tatsu
Tatsu
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307
Performance doesn't matter if you're not returning the correct results. You can't sacrifice accuracy for performance or your program will be worthless. In the case you mention, "var1 IS NULL" is the only option that guarantees accuracy every time. I can't say for certain but I would expect that to perform better since it is the default but I doubt there is any siginificant different either way.

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1168

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.


J D-238096
J D-238096
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 18

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


Yelena Varshal
Yelena Varshal
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3480 Visits: 593

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




Regards,
Yelena Varshal

Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20941 Visits: 9671
That'd be a nice syntaxe : @SomeVar = oppsWTFIsItNull(@SomeVar, 0)
.
cs_troyk
cs_troyk
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1529 Visits: 965

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





J D-238096
J D-238096
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 18

[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.


Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1353 Visits: 1168

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.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search