Hugo Kornelis (4/18/2015)
I personally think the first case is already crazy (who would ever type a literal NULL as the first argument to ISNULL anyway??). But for the sake of geeking out, I decided to do some experimenting.
Yes, you are right, it is just as crazy as the other case. In both cases the only reason to do it is geekery, trying to find out what happens.
ISNULL(NULL, NULL) does indeed evaluate to int. I verified this by running the code below and then checking the table properties in SSMS.
SELECT ISNULL(NULL,NULL) AS x INTO dbo.y1;
Unfortunately that doesn't tell us that ISNULL is returning a NULL typed as INT: this too generates atable with an INT column
so ISNULL could be returning an untyped NULL and the generated column would still be type INT. of course i should be impossible for a function to return an untyped NULL, but we are in weird territory here.
However - it does get more interesting. Because my next experiment, running the code below, should have resulted in an error when trying to convert the string constant 'x' to an integer, but instead it created a table with a varchar(2) column - so now the data type resulting from ISNULL has apparently suddenly changed to varchar(1)!!
SELECT ISNULL(NULL,NULL) + 'x' AS x INTO dbo.y2;
[/quote-2]
That's very interesting. Thanks for pointing it out, Hugo.
It should work if the type of the result from ISNULL has lower precedence that a string tpe, so it would work if the "value" delivered by ISNULL(NULL,NULL) is an untyped NULL.
So I think your experiments have demonstrated that I was wrong about the result being a NULL of type INT, and that this function can after all return an untyped NULL as its result.
As I said before, all very weird, completely non-standard. One of many reasons why I try to avoid ISNULL and instead use COALESCE whenever I can.
Certainly it's weird. But it's less weird than calling a function with COALESCE(<expression>,0) as its argument and getting an error because the argument is NULL.
ISNULL works sensibly under any isolation level, COALESCE is stable only under snapshot and serializable isolation levels, and shouldn't be used under repeatable-read or read-committed isolation levels without being absolutely certain either that it doesn't matter if the instability causes a NULL to be returned which wouldn't have been returned if coalesce were deterministic/stable or that other actions going on can't make this happen. The nullability of a non-null result from COALESCE can cause problems too (in SELECT...INTO statements). These problems with coalesce are pointed out very clearly in BOL, with advice that in situations where a spurious NULL can cause problems one should either use ISNULL instead of COALESCE or use a high isolation level so that COALESCE won't cause instability problems.
It's also unfortunate that the SQL specification didn't state that errors caused by speculative evaluation of non-chosen paths should be fully contained, as they are in every other language I've worked with that uses speculative evaluation.
COALESCE is attractive because (a) it is standard and (b) it has a neater syntax than ISNULL; and it's unattractive because (a) it's unstable except at the two highest isolation levels and (b) its result always has a nullable type and (c) it can deliver null where ISNULL wouldn't and (d) it forces use of some sort of work-around (either ISNULL or something much nastier) to avoid unwanted errors during COALESCE's speculative evaluation.
I think that most of the time I can use COALESCE, but it's clear that there are situations where COALESCE is dangerous and unusable and ISNULL is the tool for the job - when the problem with COALESCE is the speculative evaluation of results imposed by its description as a CASE statement the only way to avoid using ISNULL is to write a series of IF statements to do the coalesce function safely - and I think that using control flow because the standard forgot to say that speculative evalution is required to be safe is a worse option than using ISNULL.
Tom