Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Four Rules for NULLs Expand / Collapse
Author
Message
Posted Saturday, June 04, 2005 1:33 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:18 PM
Points: 1,276, Visits: 1,132
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls.asp
Post #187658
Posted Monday, June 27, 2005 7:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 22, 2008 8:45 AM
Points: 391, Visits: 57

Would it matter, performance wise using

var1 IS NULL

instead of

var1 = NULL

?

Post #194280
Posted Monday, June 27, 2005 7:44 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 291, Visits: 253
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
Post #194288
Posted Monday, June 27, 2005 8:34 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:18 PM
Points: 1,276, Visits: 1,132

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.

Post #194340
Posted Monday, June 27, 2005 9:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 25, 2012 9:42 AM
Points: 10, Visits: 15

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

Post #194358
Posted Monday, June 27, 2005 10:26 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, December 26, 2013 9:45 AM
Points: 3,475, Visits: 577

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

Post #194414
Posted Monday, June 27, 2005 10:31 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
That'd be a nice syntaxe : @SomeVar = oppsWTFIsItNull(@SomeVar, 0)
.
Post #194417
Posted Monday, June 27, 2005 10:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 6:14 PM
Points: 1,304, Visits: 776

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




Post #194424
Posted Monday, June 27, 2005 10:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 25, 2012 9:42 AM
Points: 10, Visits: 15

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

Post #194430
Posted Monday, June 27, 2005 11:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:18 PM
Points: 1,276, Visits: 1,132

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.

Post #194437
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse