• I'm late to the party, as it were, so I did not read through each of the posts in the argument between Tom and Paul. So this is not to say that I'm siding with Tom over Paul, or vice versa, but here is my take.

    The original explanation for the correct answer that I saw was:

    "In SQL Server 2005 the IsNull() function will truncate the length of replacement_value to that of check_expression. "

    1. I didn't find the word "truncate" anywhere on the referenced page (URL given below, from the answer):

    http://msdn.microsoft.com/en-us/library/ms184325%28SQL.90%29.aspx

    So, given that the word "truncate" is not in the explanation at the referenced page, I think it is fair to expect at least a little explanation (if it is known) why this behavior is. After all, I agree with Tom that it is counterintuitive to provide a function that ostensibly allows you to provide replacement text of your choice, when in reality that text may be truncated to the exact type AND length of the type specificed. Even if one thinks that this is a logical leap to make based on common sense, it is not a connection that can be made from the explanation in the reference, since there's no explicit reference to truncation. Even the examples given don't use varchar. Of course, it is fair to expect a diligent DBA to do further research and find other examples, but that doesn't resolve the explanation gap in referring to "truncate" when that term is not present in the referenced URL.

    2. Going back to the ostensible logic of the ISNULL behavior, I can certainly understand why Tom would be, if not flabbergasted, then baffled (as Paul put it) by this behavior, prior to learning more about it. Tom did say in his original comment that he had learned from it and that "anything that makes me learn is good from my point of view." But I agree with Tom that this is not intuitive behavior. Here is why I think it's not intuitive. The second parameter for the ISNULL function is, supposedly, the exact text you want to output in case the first parameter to the function is NULL. But the second parameter is not guaranteed to be the exact output. It's really the exact output, constrained to the data type AND length of the check expression. Since length isn't mentioned explicitly, to me that's an explanation gap that would benefit from clarification for pedagogical purposes - for those of us who don't always make such leaps intuitively.

    I'm not sure if this confirms that ISNULL's behavior is not intuitive, but I Googled "ISNULL() and varchar" and found a page with this sample code, showing that the COALESCE function behaves differently and returns the exact replacement text (at least in this example).

    DECLARE @t VARCHAR(2)

    SELECT ISNULL(@t, 'Open'), COALESCE(@t, 'Open')

    ---- ----

    Op Open

    (1 row(s) affected)

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/b221fb3e-c23e-49d5-b7ed-38ed7cab8238

    Thanks,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html