27

Given the following:

SELECT ISNULL('XY' + NULL, 'ABCDEFGHIJ') -- Outputs ABC (Why?)
SELECT COALESCE('XY' + NULL, 'ABCDEFGHIJ') -- Outputs ABCDEFGHIJ

Why are these statements returning different results?

natenho
  • 5,231
  • 4
  • 27
  • 52

3 Answers3

27

According to Microsoft documentation, for function:

ISNULL(check_expression, replacement_value)

replacement_value must be of a type that is implicitly convertible to the type of check_expression. Note that type for 'xy'+NULL is VARCHAR(3). Because of this your string 'ABCDEFGHIJ' is cast to VARCHAR(3) and thus trimmed.

It sounds strange why it is not VARCHAR(2), but this is the way it is - one character longer than 'xy'. You can play with this SQLFiddle and see for yourself that type for 'xy'+NULL is the same as for expression CASE WHEN 1=2 THEN 'XYZ' ELSE NULL END, which is NULL but is implicitly compatible to VARCHAR(3).

It seems that for expression 'xy'+NULL perceived length can be computed as 'xy' string length (2) plus 1 for every NULL added. For example, type of 'xy'+NULL+NULL is VARCHAR(4), type for 'xy'+NULL+NULL+NULL is VARCHAR(5) and so on - check out this SQLFiddle. This is extremely weird, but that is how MS SQL Server 2008 and 2012 work.

mvp
  • 111,019
  • 13
  • 122
  • 148
  • Good explanation, although I'm not convinced about the varchar(3) instead of varchar(2) - I can't find it documented anywhere. – natenho Sep 19 '13 at 02:41
  • 2
    I must increase the information here to explain more about the COALESCE behavior over the ISNULL behavior, from Microsoft [documentation](http://technet.microsoft.com/en-us/library/ms190349.aspx): The COALESCE expression is a syntactic shortcut for the CASE expression. That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression: CASE WHEN (expression1 IS NOT NULL) THEN expression1 WHEN (expression2 IS NOT NULL) THEN expression2 ... ELSE expressionN END -> The case return type is given by the highest expr precedence. – natenho Sep 19 '13 at 02:49
  • This explains `COALESCE` behavior, but does not say anything why type of `'xy'+NULL` is `VARCHAR(3)`. This must have something to do with SQL Server internals. – mvp Sep 19 '13 at 02:53
  • I'm not sure if I get the proof by your sample. The 2nd query is a CASE that is a varchar(3) because of 'XYZ', not because of NULL, right? So I guess the remaining doubt is about 'X'+NULL becoming varchar(2) instead of varchar(1). So, is NULL considered as a single char in this CASE? Do I need to concern about overflows/truncation when using ISNULL concatenating strings? – natenho Sep 19 '13 at 02:56
  • 1
    Yes, NULL is adding 1 more character. This is super-strange, but here is definitive proof: http://sqlfiddle.com/#!3/d41d8/20994 – mvp Sep 19 '13 at 03:00
  • Fascinating trivia about 'AA' + NULL + NULL - I submitted a PR to MicrosoftDocs/sql-docs to document this behavior. I do not believe I've ever seen this behavior in my 11 years working with SQL Server. – John Zabroski Mar 01 '19 at 23:16
4

You can check all the difference here, its very clear

MSDN : http://msdn.microsoft.com/en-us/library/ms190349.aspx

MSDN Blog : http://blogs.msdn.com/b/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx

Viji
  • 2,629
  • 1
  • 18
  • 30
0

ISNULL() converts the replacement value to the type of the check expression. In this case, the type of the check expression is CHAR(2), so converting the replacement value truncates it (are you sure you're getting ABC and not just AB?).

From the Microsoft documentation:

replacement_value can be truncated if replacement_value is longer than check_expression.

Barmar
  • 741,623
  • 53
  • 500
  • 612