CAST(NULL AS varchar(1))

Question

Post reply

CAST(NULL AS varchar(1))

BOR15K

SSCertifiable

Points: 5894
More actions
January 13, 2021 at 1:27 pm

#3831920

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.

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

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

ratbak Ten Centuries Points: 1380 More actions · Answer 1

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

BOR15K SSCertifiable Points: 5894 More actions · Answer 2

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

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?

Thom A SSC Guru Points: 99133 More actions · Answer 4

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.
Larnu.uk

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 5

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.

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

ScottPletcher SSC Guru Points: 101117 More actions · Answer 6

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 7

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.

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions