Paul White NZ (4/8/2010)
I see nothing wrong with the original explanation. It correctly conveys the reason that the result is 'IsUnk' and not 'IsUnknown'.
I have to disagree. What you are telling me is that the length of the check expression is 5. That is pure nonsense.
The check_expression here is the result of applying LEFT(..., 5) to a VARCHAR(50) NULLable column. The type of check_expression is clearly VARCHAR(5) NULLable. The replacement_value is implicitly converted to that type, resulting in truncation - exactly what the explanation conveys.
Yes that's what is happening (except that I would take issue with the word "clearly", see below) . The explanation completely fails to convey that, it says something completely different is happening. It does give a pointer to part of the correct explanation.
[
What is interesting here is that left(X,5) delivers an expression of type varchar(5), not an expression of the same type as X. I haven't seen that documented anywhere, and I'm flabbergasted by it. Needless to say I got the wrong answer, and I've learnt this crazy behaviour of Left from it. I guess that makes it a good question - anything that makes me learn is good from my point of view.
LEFT is documented as returning (n)varchar...what is it exactly that baffles you about an expression with a defined maximum length of 5 being returned as (n)varchar(5)?
From Expressions (Transact-SQL): When two expressions are combined by using arithmetic, bitwise, or string operators, the operator determines the resulting data type.[/quote]
On that logic I could say what's wrong with * (or + or -) returning bigint when it needs to, instead of an error. But in fact
declare @x int = 214735276, @y int = 1073741827
select @x*@y
doesn't return 230570247573589252 which on your logic it ought to return: what would baffle you about a multiplication which returns a number between 2**62 and 2**64 returning a bigint, as it's obvious from the parameters that it won't fit a smaller type? But in that case the design decision was to return an error, and I guess you agree with it despite your interpretation of that "the operator determines the type" statement. In the case of left the decision was to make an arbitrary type change depending on the parameters - there's no consistency there, and cetrainly no clarity.
It would be nice if this case where an arbitrary type change is made were documented, but as far as I can tell it isn't.
None of that of course detracts from it being a good question with a correct answer. But I still think the explanation is wrong - it would have been improved by a change to say simply "see <BOL reference>" without the incorrect statement that actually precedes that.
Tom