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

  • SanDroid (11/12/2010)

    ...true NULL is a lack of anything at all.

    However, SQL server has to use something to maintain that the 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.

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

  • Nice clean question, answer, and explanation. Thanks.

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


  • Thanks for the question.

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

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

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