Using ISNULL when adding NULL to varchar

Whilst experimenting with MSSQL I came across some behaviour I cannot explain.

I was looking at what happens to a NULL value when it is added to a varchar, and when I do the following query:

SELECT
   ISNULL(NULL + ' ', 'test')

I get the result ‘te’. Similarly if I change the word test for any other word I only get the first two letters. If I increase the number of spaces in the + ‘ ‘ I get extra letters in my result (so NULL + ‘[two spaces]’ gives me ‘tes’). Any ideas what is going on?

If I declare a variable and set it to NULL e.g.

DECLARE @testnull AS varchar(32)

SET @testnull = NULL

SELECT
ISNULL(@testnull + ' ', 'test')

then I get the result ‘test’ (as I would expect).

Answer

Use COALESCE. ISNULL takes the first NON-NULL data type, and in this case because it hasn’t been declared, you get a VARCHAR(1) for the NULL and then it becomes a VARCHAR(2) when you add the space (this is still NULL when evaluated but SQL Server makes an assumption before that step). In some cases the default for varchar without length is 1, and in others it is 30. I’ll let you guess which one is being used here.

SELECT
 ISNULL(NULL + ' ', 'test'), COALESCE(NULL + ' ', 'test');

Results:

----  ----
te    test

You can see this explicitly by:

SELECT
   x = ISNULL(NULL + ' ', 'test')
   INTO #a1;

SELECT
   x = COALESCE(NULL + ' ', 'test')
   INTO #a2;

SELECT LEFT(t.name, 3), TYPE_NAME(c.user_type_id), max_length
FROM tempdb.sys.columns AS c
  INNER JOIN tempdb.sys.tables AS t
  ON c.[object_id] = t.[object_id]
  WHERE t.name LIKE '#a[1-2]%';

Results:

---  -------  ----
#a1  varchar     2
#a2  varchar     4

In almost all cases, I prefer COALESCE over ISNULL. I explain why (and where the exceptions exist) in this tip:

• Deciding between COALESCE and ISNULL in SQL Server