SQL Server Performance

CAST(Null As varchar (25)) As CFDCode

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, Jun 26, 2009.

  1. atulgoswami New Member

    Could there be any possibility to cast NULL value in any situation?
    to any data type.
    NULL is independent of data type.
    I was just going through one SQL script and came across this.
    Thanks
  2. FrankKalis Moderator

    Interesting question. [:)]
    Never thought about it, but from my understanding NULL itself is type-less.
    The only reason I can imagine right now where it may make sense to explicitly CAST (NULL to some other type) is when you create a table via SELECT INTO. Try this:
    SELECT
    NULL AS Column1
    INTO
    dbo.Frank1

    EXEC sp_help 'dbo.Frank1';

    DROP TABLE dbo.Frank1;
    You'll find that by engine default, the datatype is assumend to be an int. Nowe try this:

    SELECT
    CAST (NULL AS varchar(25)) AS Column1
    INTO
    dbo.Frank1

    EXEC sp_help 'dbo.Frank1';

    DROP TABLE dbo.Frank1;
    Now you see that you forced the table to be created with a varchar(25) column.
  3. Adriaan New Member

    Hm, isn't that why we have the ISNULL() function?
  4. FrankKalis Moderator

    [quote user="Adriaan"]
    Hm, isn't that why we have the ISNULL() function?
    [/quote]
    Not sure I understand you here. [*-)]
  5. Adriaan New Member

    The question was "Could there be any possibility to cast NULL value in any situation? to any data type."
    ISNULL ( check_expression , replacement_value )
    Works like a CAST without a data type specification:
    SELECT ISNULL ( NULL, '' ) -- returns an empty string
    SELECT ISNULL ( NULL, 1 ) -- returns 1.
    The data types of the two parameters need to match up nicely, otherwise you may get some nasty "Conversion failed when converting ... from ... " error messages.
  6. FrankKalis Moderator

    But then you're still kind of depending on the interpretation of the datatype by the engine.
    The first case would give a (var)char(1) datatype while the second gives an int, which may or may not what you want, since you could have wanted a tinyint or even a bit instead.
    Another interesting thing to consider is here that the datatype precedence rules apparently apply. Say, for example, you want for some reason build up a "truth choices" table with the values 0, 1, NULL. If I were to build such a table, I would choose a bit datatype.
    When you use the SELECT INTO syntax from my first reply, the batch may look like this
    SELECT
    *
    INTO
    dbo.Frank1
    FROM
    (SELECT
    CAST (NULL AS bit) AS Column1
    UNION ALL
    SELECT
    0
    UNION ALL
    SELECT 1) x
    Now guess what datatype Column1 in the table now has?
    EXEC sp_help 'dbo.Frank1';
    It's an int. The engine regards the values 0 and 1 as of type int even though one might expect the CAST(NULL AS bit) would be enough to indicate the type of the column. However, int has a higher precedence than bit and so implicit conversion take places and leaves you with a different datatype.
    Does that prove anything? Good question. At least it proves that it's better to be always explicit in what you want SQL Server to do. [:)]
  7. Adriaan New Member

    I couldn't agree more - it's high time they dropped the SELECT INTO syntax from the SQL standard.[6][<:eek:)]
  8. atulgoswami New Member

    Adriaan,FrankKalis
    I owe and indeed it was a great learning for me from both of you. Basically i am working on performance review and have so many questions related to this. I'll post one by one for such a wonderfull learning.
    Thank you very much.
  9. Madhivanan Moderator

    Yes the precedence of the datatype plays a major role in this case
    See what happens when you run these
    1 SELECT
    ISNULL ( NULL, 1 )union all
    SELECT ISNULL ( NULL, getdate() )
    2SELECT
    ISNULL ( NULL, 1 )union all
    SELECT
    ISNULL ( NULL, 19879.56)3
    SELECT
    ISNULL ( NULL, 1 ) union all
    SELECT
    ISNULL ( NULL, 'this is test to see the data type of null' )

Share This Page