Paul White NZ (4/9/2010)
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:
That exactly matches my reaction to your comments! :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.
I wish you would pay some attention to what I say instead of to what you want to pretend I said. I think my comments make it absolutely clear that I know perfectly well that IsNull truncates to the length embedded in the type of the check argument when the check argument is one of the character or binary types. Your claim that I don't understand that is just plain outrageous. The reason (as you well know, since it was there plainly and clearly in my original post, so I can only assume you are being deliberately inaccurate on this) that I got the wrong answer is that I thought the left function returned a value with the same type as its first argument.
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.
I don't want you to locate that stuff for me - you'd have a job anyway, as it isn't there (at least google can't find it, nor bing, nor MSDNs own search).
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:
I haven't changed what I'm saying. The explanation is incomplete. What there is of it is also incorrect, because it talks of LENGTH (meaningless since it has to apply to NULL, whose length is also NULL) instead of TYPE. How is that different from "incorrect and incomplete"? Are you suggesting that the length of the result is NULL (the LENGTH of the first argument) rather than 5 (the length embedded in the TYPEof the first argument)?
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.
Exactly. It is the TYPE that is at issue. It can hardly be the LENGTH of an expression whose value is NULL, can it? It really irritates me that you keep on insisting that "LENGTH" is correct and that I'm wrong or pedantic (that does seem to be a bit of a retraction from wrong though, doesn't it) to say that it's the TYPE that matters, since you clearly do understand full well that it's the TYPE (and the length that is embedded in that TYPE) not the LENGTH of the argument that counts.
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.
As I have pointed out before, that is not an answer. It is a tautology that a function determines the type of its result. That doesn't tell anyone, for any particular function, what that type is for given arguments - that needs to be specified.
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.
And you may believe it is documented: that does not make it so either. I've done a pretty thorough search of the documentation, using tools that I would expext to find the definition of how left determines the result TYPE ( as opposed to the result LENGTH for non-null cases) if such a definition were present - and it has turned up nothing, so I have good grounds to believe it is not documented. That's not a refusal to check the documentation - it's the result of a very thorough check - more thorough than you've undertaken, I suspect, since you are unable to provide any reference that supports your claim that the type of the LEFT functions result is documented. You continue to assert that the definition is there, but refuse to give a reference. Well, why should I believe you when you present no evidence?
Tom