COALESCE Vs ISNULL

Question

COALESCE Vs ISNULL

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

SanDroid SSChampion Points: 10068 More actions · Answer 1

Nice question. I learned something new about the Coalesce function and NULL values.

IMO: The actual data type for a NULL value once inserted into a table has to do with the fact that a true NULL is a lack of anything at all.

However, SQL server has to use something to maintain that the feild should return a NULL so it uses an int.

Rose Bud Hall of Fame Points: 3147 More actions · Answer 2

SanDroid (11/12/2010)
...true NULL is a lack of anything at all.
However, SQL server has to use something to maintain that the feild...so it uses an int.

That's my guess, too. NULL doesn't have a default datatype but a column in a table must have a default datatype and that default is int.

Hopefully, someone can confirm this theory.

Dean Cochrane SSC Eights! Points: 992 More actions · Answer 3

I think this difference in behavior may have something to do with the fact that the construction we're all so used to in SQL Server

SELECT (some thing or expression)

is really a short hand version of

SELECT (some thing or expression)

FROM (some table)

In Oracle and DB2, you can't write

SELECT (something)

You have to specify the FROM clause - which I believe is consistent with the language definition. Oracle gets around this by providing DUAL. DB2 shops often create their own version.

Anyway, I think that this difference in behavior is due to the fact that 1. ISNULL is old, proprietary, and non-standard, and 2. SELECT NULL without a FROM clause is really shorthand for SELECT NULL FROM SomeConvenientTable.

Personally, I always use COALESCE. It is standard SQL, and it is more flexible than ISNULL. ISNULL can do nothing that COALESCE can't do, and COALESCE can do things that ISNULL can't. So... COALESCE, always.

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Nice clean question, answer, and explanation. Thanks.

But I must remember not to do QOTD at this time of day.

Tom

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events