Tom.Thomson (4/9/2010)
I have to disagree. What you are telling me is that the length of the check expression is 5. That is pure nonsense.
The type of the check expression is VARCHAR(5) NULLable. It has a maximum length of 5. What is nonsensical or even slightly hard to understand about that?
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.
No. It conveys the crucial point without being verbose.
On that logic I could say what's wrong with * (or + or -) returning bigint when it needs to, instead of an error.
If you look up the multiply operator in Books Online, you will see that it "Returns the data type of the argument with the higher precedence". Decided by the operator, as I said before.
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's clear and consistent - it is decided by the operator. Try it with other functions, computed columns, SELECT...INTO, parameterization, and so on and so on. All work the same, consistent and clear.
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.
I can only suggest you keep looking - most behaviour is documented in Books Online in detail.
...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.
The vast majority of people are looking for a 'bite-sized' explanation - not just a lazy link to BOL. The lack other people in this thread whining about any perceived inadequacies in the 'bite-sized' answer seems to indicate that your view is in the minority.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi