Got to love the "somehow"...

Since NULL can be of any datatype, SQL Server always has a hard time determining what datatype was meant when it encounteres the constant NULL. In this case, since it's appended to a string, it's obviously a string as well. So that makes it a varchar (char could have been used as well, but SQL Server chooses varchar in these cases). Both of these have a default length of 1.

The result of concatenating varchar(5) ('abcde') with varchar(1) is of course varchar(6), so that is the datatype of the first argument. And since ISNULL returns the same datatype as the first argument, the result is also varchar(6).

Here is some code to repro this:

SELECT 'abcde' AS a, 'abcde' + NULL AS b INTO TestTable;

go

EXEC sp_help TestTable;

go

DROP TABLE TestTable;