• Tom.Thomson (4/9/2010)


    What is nonsense is your previous statement that the explanation says this. It doesn't. The explanation says that the result is truncated to the length of the check expression (implying that the length of NULL is 5 here). That is NOT remotely the same thing.

    Good grief, man! :doze:

    The explanation says "the IsNull() function will truncate the length of replacement_value to that of check_expression." The check_expression has a type of VARCHAR(5). The replacement_value has an implied type of VARCHAR(9). Many people taking the question would expect the result to be 'IsUnknown', and would be surprised that it was 'IsUnk'. This represents by far the majority of wrong answers - and, I would wager, was the main point of the question.

    The explanation succinctly expresses the reason for the unexpected answer - the VARCHAR(9) is truncated to VARCHAR(5) by the ISNULL function. It really is as simple as that.

    The fact that you, and the other 19% that selected the wrong answer, did not know this, simply means you have learnt something, which is the point of the QotD. Your point about 'the length of NULL' is irrelevant and shows your lack of understanding - not that of the questioner.

    The best you've offered is a pointer to the general statements that functions determine the results of their results. That doesn't actually tell me a single thing about what left determines as the type of its result. I'm pretty sure that this is NOT documented in BoL - sure enough that I'll waste no more time looking for it.

    I am not here to locate stuff in Books Online for you - if you want to expand your understanding, you will have to put some effort in. I would encourage you to look into how SQL Server determines the type of an expression in general - for computed columns, SELECT...INTO statements, parameterization, and so on. It is logical, consistent, and documented.

    The explanation is incorrect (only the type of the check argument affects truncation of the result, not its length) and incomplete (no mention of the type of the result of the left function).

    Are you saying the explanation is incorrect (as in all your previous statements) or that it is just incomplete now? You seem to be moving from absolute statements like 'nonsense', 'wrong' and 'incorrect' to something closer to 'well, technically, it might be more accurate to say...' :laugh:

    To avoid any danger of coming off as a tedious, overly-academic, pedant - and there is a risk - I would encourage you to accept the explanation for what it is: a simple statement. A QotD explanation is not required to explore ever possible nuance. In any case, the type of check_expression defines its maximum length.

    I find the behaviour of the left function surprising and consider it not consistent with the behaviours of arithmetic functions, which have the advantage of being well documented.

    I believe I have answered this already: The operator determines the type of the result, in all cases.

    Your refusal to check the documentation disqualifies you from making absolute statements about what is, and is not, documented. You may find it surprising, you may consider it inconsistent, you may think it's wrong, nonsense or whatever. That doesn't make it so.