CAST(NULL AS varchar(1))

  • Hello All,

    I have been tasked to improve a very old SQL code we have and first thing I see (may not necessarily be the candidate for performance improvement though) going through the code is many CASE records with CASE WHEN myColumn IS NULL THEN CAST(NULL AS varchar(1)) ....

    Can one advise why someone would use such an expression, please?

    CASE WHEN myColumn IS NULL THEN NULL ... seems to be working very well.

  • What data type is myColumn? What data type is the expression returned if myColumn is not null?

    In this example, NULL resolves as an int if it's the only value:

    DROP TABLE IF EXISTS #myData;

    WITH myData AS
    (SELECT NULL AS myColumn
    --UNION ALL
    --SELECT 'abcdefghijklmnopqrstuvwxyz' AS myColumn
    )
    SELECT mycolumn -- CASE WHEN myColumn IS NULL THEN CAST(NULL AS varchar(1)) ELSE myColumn END AS myColumn
    INTO #myData
    FROM myData;

    Select columns.object_id,columns.column_id,columns.name AS ColumnName,columns.system_type_id,types.name AS TypeName,columns.max_length,columns.precision,columns.scale,columns.collation_name
    FROM Tempdb.Sys.Columns
    INNER JOIN sys.types ON types.system_type_id = columns.system_type_id
    WHERE Object_ID = Object_ID('tempdb..#myData')

    DROP TABLE IF EXISTS #myData;

    But using the case expression causes it to resolve as a varchar(1).

  • Yes, I also thought about it, but the following example shows it doesn't matter - SQL Server implicitly converts NULL to the correct type, no?

    DECLARE @t AS TABLE (A INT);
    INSERT INTO @t
    VALUES( CAST(NULL AS VARCHAR(1)))
    SELECT * FROM @t
  • I think it would depend on what you're doing with the value AFTER the case statement.  for example, I've had to do odd things like the above when trying to concatenate a bunch of things and you don't want a null back because a single column is null.

    The snippet posted doesn't really help with that.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Casting a literal NULL to a data type in a CASE is somewhat pointless; it'll be converted to the highest precedence data type of the non-NULL literals or column (which doesn't need to be non-NULL). Removing such CAST functions is, however, unlikely to give much of, if any, performance benefit.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • BOR15K wrote:

    Yes, I also thought about it, but the following example shows it doesn't matter - SQL Server implicitly converts NULL to the correct type, no?

    DECLARE @t AS TABLE (A INT);
    INSERT INTO @t
    VALUES( CAST(NULL AS VARCHAR(1)))
    SELECT * FROM @t

    First of all, having a VARCHAR(1) is a really bad idea.  It takes two additional bytes for SQL Server to mark the column size.  In the case above, I agree... for inserts, there's no need to cast a NULL to anything unless you're using SELECT/INTO and are using that to define what the column should actually be.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden wrote:

    BOR15K wrote:

    Yes, I also thought about it, but the following example shows it doesn't matter - SQL Server implicitly converts NULL to the correct type, no?

    DECLARE @t AS TABLE (A INT);
    INSERT INTO @t
    VALUES( CAST(NULL AS VARCHAR(1)))
    SELECT * FROM @t

    First of all, having a VARCHAR(1) is a really bad idea.  It takes two additional bytes for SQL Server to mark the column size.  In the case above, I agree... for inserts, there's no need to cast a NULL to anything unless you're using SELECT/INTO and are using that to define what the column should actually be.

    I'd stick with varchar for this specific use.  I've just had too many odd results when a fixed char was involved, even though varchar has higher precedence.  As to performance, this is a one-time value, so the performance "hit" will be miniscule.

    However, for a table column, definitely use char(1) rather than varchar(1), due to performance, but within characters expressions, personally I stick to varchar.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • I've never run across any "odd results" by using CHAR(1) in such a SELECT, yet.  With that in mind, do you have an example of what happened so 1) we know what to look for and 2) since that sounds like a serious bug, someone could do more testing to demonstrate the issue to MS on the feedback site to get it fixed?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • In the specific code you posted, I agree that the CASE statement makes no sense. If does, however, depend on what you're doing with it.

Viewing 9 posts - 1 through 9 (of 9 total)

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