SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by ken.trock / Column length / Latest Posts

RE: Column length

webrunner — Mon, 12 Apr 2010 14:12:35 GMT

[quote][b]Tom.Thomson (4/12/2010)[/b][hr]Oh dear :unsure:. This makes me think maybe Paul was right when he said my post was unclear. [/quote]No, you're right. As I said, I didn't read the whole thread carefully, and may have projected my own reading of what is counterintuitive onto your comments about the LEFT function. I re-read your initial comment and it is indeed about the LEFT function behavior.I still think it would help if the ISNULL documentation had a varchar example, though.[quote][quote][b]webrunner (4/12/2010)[/b][hr]IAfter 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. [/quote]Actually I don't think it's counterintuitive - the type of the result is the same as the type of the check expression, and in the case of varchar, varbinary, and nvarchar the type includes specification of the maximum permitted length. It's the behaviour of LEFT that surprised me, because I expected it to deliver a result with the same type as its first argument, just as IsNull does. [/quote] Interesting - by "type" do you mean type and length, such as varchar(10)? In that case, I never thought about it, but it does make sense. A LEFT function performs a kind of substring operation, so I can see that it would return a value matching the length chosen in the second parameter. If by "type" you mean type only (varchar vs. varbinary, etc.), though, I just thought the ISNULL function would override that and ensure that the result of ISNULL would be long enough to accommodate the entire replacement value chosen. That was ignorance on my part - in reality it seems one just has to be careful to make sure the lengths work out; I guess it may also be ignorance on my part not to be surprised by the behavior of LEFT(). :-)[quote][quote]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. [/quote] The IsNull behaviour is nice and clean and tidy, Paul and I have no disagreement on that. In fact what we mostly disagreed about was words (words badly chosen by each of us, I think) not anything in T-SQL.[/quote] Yes, as I said above, if "type" means "type and length," then the ISNULL documentation make sense to me now. As far as LEFT is concerned, I can see the logic of returning the length that was chosen for the second parameter, but I suppose I can see the logic of keeping the length that was passed in. I guess the existing behavior didn't surprise me as much as it did you.I don't totally agree with you guys regarding ISNULL with varchars - especially since it seems that the existence of the COALESCE function** means that someone thought returning the whole string would be useful in certain cases - but I think as long as the behavior of ISNULL with a varchar is explained properly I can live with it.** For example:DECLARE @t VARCHAR(4)SELECT ISNULL(LEFT(@t,2), 'Open'), COALESCE(LEFT(@t,2), 'Open')[quote]On a different topic: my first QoTD is due to appear in 8 to 10 days time; I've tried to choose something that I can't get wrong, so that I don't suffer the bashing that many QoTD setters get, but I'm not at all sure I've succeded.[/quote]Good for you. I've never tried a question and, frankly, still consider myself unqualified to post one that wouldn't get torched for inaccuracy.Thanks,webrunner

RE: Column length

L' Eomot Inversé — Mon, 12 Apr 2010 13:24:23 GMT

Oh dear :unsure:. This makes me think maybe Paul was right when he said my post was unclear.[quote][b]webrunner (4/12/2010)[/b][hr]IAfter 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. [/quote]Actually I don't think it's counterintuitive - the type of the result is the same as the type of the check expression, and in the case of varchar, varbinary, and nvarchar the type includes specification of the maximum permitted length. It's the behaviour of LEFT that surprised me, because I expected it to deliver a result with the same type as its first argument, just as IsNull does. [quote]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. [/quote] The IsNull behaviour is nice and clean and tidy, Paul and I have no disagreement on that. In fact what we mostly disagreed about was words (words badly chosen by each of us, I think) not anything in T-SQL.On a different topic: my first QoTD is due to appear in 8 to 10 days time; I've tried to choose something that I can't get wrong, so that I don't suffer the bashing that many QoTD setters get, but I'm not at all sure I've succeded.

RE: Column length

webrunner — Mon, 12 Apr 2010 10:28:49 GMT

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):[url=http://msdn.microsoft.com/en-us/library/ms184325%28SQL.90%29.aspx ][u]http://msdn.microsoft.com/en-us/library/ms184325%28SQL.90%29.aspx [/u][/url]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)[url=http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/b221fb3e-c23e-49d5-b7ed-38ed7cab8238][u]http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/b221fb3e-c23e-49d5-b7ed-38ed7cab8238[/u][/url]Thanks,webrunner

RE: Column length

L' Eomot Inversé — Sun, 11 Apr 2010 14:15:37 GMT

[quote][b]Paul White NZ (4/11/2010)[/b][hr] Psychic Tom strikes again! Not only did you "know" how much documentation I had checked earlier, you now "know" that I am 'quite clearly' distorting your words! [/quote] Of course I can know that without being psychic. I just have to look at what I wrote and at how you quote it, and observe that it is distorted. [quote]How do you know that? Why would I do that? What would be my motivation? [/quote] As noted, I know it by observing it. However, it was unfair of me to say the distortion is deliberate and I withdraw that. I actually haven't a clue why you did it, I guess it has to be an error, so "deliberately"was unfair - as bad as some of your comments. [quote]If you really want to know, I find your replies hard work: they are too long, and you do not communicate at all clearly. I don't have all day to try to decode what you write, you know. [/quote]Maybe they are too long - I think that's a side effect of the degree of irritation that you sometimes manage to induce in me. I think they are quite clear though, but obviously I'm not meeting the standard of clarity that you expect; you might take a moment to consider though that my reaction to some of your message may mean that I'm mistaking your meaning, perhaps because you are not meeting the standard of clarity that I expect.[quote] [quote]Can you tell me how "it's incomplete. What there is of it is incorrect" means "not incorrect", how it means "incomplete" alone and doesn't suggest "incorrect"? Or even how it is unclear?[/quote] Yes Tom, I can. It is an unclear and bizarre construction. I'm not sure even you know what you mean here. Is it merely incomplete? Or is it incorrect? I don't know why you insist on writing two awkward sentences when a couple of words will do, really I don't. Do you do it intentionally? [/quote]Seriously I just don't know what to make of that. There are two statements. (1) It's incomplete. (2) What there is of it is incorrect. The phrase "What there is of it" is extremely common British and American English and means the available part of something incomplete; maybe this phrase isn't used n NZ? To me it's neither unclear nor bizarre, it's the sort of thing I have been used to seeing every day in books and journals and office memos throughout ny working life (and in emails, usenet posts, web pages, and blogs since those have existed).[quote][quote]The person who had asked for more explanation appeared to like it. That matters more to my than all your venemous ranting[/quote]Well, that's rich! I would invite you to read your own submissions before casting aspersions. [/quote] I guess it's fair repayment for your remarks about wriggling etcetera? In any case, I never cast nasturtiums, only their seeds.:-D[quote] [quote]I, and probably most civilised people regard that sort of deliberate selection of part of a statement to give the impression that something was said that clearly was not said[/quote]Hmmm...civilised might be a bit strong, given your performance here. Look, I've already said that I have tried to be fair when quoting. Your responses are just far too long to quote in their entirety. If you feel you have been misquoted, I would invite you to consider whether that was really done deliberately and maliciously - or whether perhaps you might have been less than clear? [/quote]I don't expect you to quote everything - of course not. I do regard it as unacceptable to quote selectively in such a way as to totally distort or even reverse the meaning, and then base argument on the resulting straw man.[quote] [quote]So you continue to distort.[/quote]All I said was that I tried to be fair, and you don't express yourself well. That is my genuine perception, and even 'psychic Tom' can't argue with that. [/quote] I suppose I have to accept that you tried to be fair if you say so; but I think you failed abysmally. [quote] [quote]I think the statement you mangled was clear, and you deliberately chose to truncate it to give the impression that I had said something which I clearly diod not say.[/quote]No, Tom. You presume far too much. Your statement was not clear at all. I did not truncate it to misrepresent what I read. [/quote] As noted above, I find that pretty incomprehensible. [quote] [quote]Then why did you say that your change from "wrong" to "pedantic" was a change in position?[/quote]I didn't - I thought you were referring to yourself in the following (complete) passage:[quote]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.[/quote]:Wow:[/quote]:laugh: Oh dear, I suggest you read it again: who does it say insisted that I was wrong or pedantic? Quite clearly it says you did, I think? So the person who changed from "wrong" to "pedantic" was you. Funny that you recognised it as a change in position when you mistakenly thought it was me, isn't it.Maybe if you comment again I should reply in Gaelic :hehe:- then you won't be able to accuse me of being unclear or verbose, because I am the very model of clarity and brevity in that tongue. It is a language of such purity that only politicians can successfully be obscure and long-winded in it (and, of course, :-D17th century poets - but very many languages suffered from that problem). Or in Spanish, of which I don't know enough to be verbose (but would probably be very unclear:rolleyes:).Anyway, all this is crazy - we both have more important things to do than to throw insults at each other and continue to quarrel over trivia.

RE: Column length

Paul White — Sun, 11 Apr 2010 08:40:02 GMT

[quote][b]Tom.Thomson (4/11/2010)[/b][hr]You continue to distort - clearly quite deliberately.[/quote]Psychic Tom strikes again! Not only did you "know" how much documentation I had checked earlier, you now "know" that I am 'quite clearly' distorting your words! How do you know that? Why would I do that? What would be my motivation? If you really want to know, I find your replies hard work: they are too long, and you do not communicate at all clearly. I don't have all day to try to decode what you write, you know.[quote]Can you tell me how "it's incomplete. What there is of it is incorrect" means "not incorrect", how it means "incomplete" alone and doesn't suggest "incorrect"? Or even how it is unclear?[/quote]Yes Tom, I can. It is an unclear and bizarre construction. I'm not sure even you know what you mean here. Is it merely incomplete? Or is it incorrect? I don't know why you insist on writing two awkward sentences when a couple of words will do, really I don't. Do you do it intentionally?[quote]The person who had asked for more explanation appeared to like it. That matters more to my than all your venemous ranting[/quote]Well, that's rich! I would invite you to read your own submissions before casting aspersions.[quote]I, and probably most civilised people regard that sort of deliberate selection of part of a statement to give the impression that something was said that clearly was not said[/quote]Hmmm...civilised might be a bit strong, given your performance here. Look, I've already said that I have tried to be fair when quoting. Your responses are just far too long to quote in their entirety. If you feel you have been misquoted, I would invite you to consider whether that was really done deliberately and maliciously - or whether perhaps you might have been less than clear?[quote]So you continue to distort.[/quote]All I said was that I tried to be fair, and you don't express yourself well. That is my genuine perception, and even 'psychic Tom' can't argue with that.[quote]I think the statement you mangled was clear, and you deliberately chose to truncate it to give the impression that I had said something which I clearly diod not say.[/quote]No, Tom. You presume far too much. Your statement was not clear at all. I did not truncate it to misrepresent what I read. [quote]Then why did you say that your change from "wrong" to "pedantic" was a change in position?[/quote]I didn't - I thought you were referring to yourself in the following (complete) passage:[quote]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.[/quote]:Wow:

RE: Column length

L' Eomot Inversé — Sun, 11 Apr 2010 06:01:51 GMT

[quote][b]Paul White NZ (4/10/2010)[/b][hr][quote][b]Tom.Thomson (4/10/2010)[/b][hr]yes, that would be an improvement.[/quote]Then that is all that needed saying. You chose to make the bald statement "This explanation is wrong." - and then ramble on for pages, dodging and weaving, back-tracking, and wriggling to turn it into "incomplete". Laughable. [/quote]You continue to distort - clearly quite deliberately. Can you tell me how "it's incomplete. What there is of it is incorrect" means "not incorrect", how it means "incomplete" alone and doesn't suggest "incorrect"? Or even how it is unclear?[quote] Sknox put it better in the post previous to your first one, "This explanation feels a little light. For completeness, it should include the behavior of the LEFT() function as well."I don't know what you think your input added to that. [/quote]The person who had asked for more explanation appeared to like it. That matters more to my than all your venemous ranting,[quote] [quote]I, and probably most civilised people regard that sort of deliberate selection of part of a statement to give the impression that something was said that clearly was not said[/quote]I have tried to be fair and extract the salient points from your verbose replies. Not my fault if you can't express yourself clearly. [/quote]So you continue to distort. I think the statement you mangled was clear, and you deliberately chose to truncate it to give the impression that I had said something which I clearly diod not say. [quote]For the record, I haven't changed my position one iota.[/quote]Then why did you say that your change from "wrong" to "pedantic" was a change in position?

RE: Column length

Paul White — Sat, 10 Apr 2010 21:09:13 GMT

[quote][b]Tom.Thomson (4/10/2010)[/b][hr]yes, that would be an improvement.[/quote]Then that is all that needed saying. You chose to make the bald statement "This explanation is wrong." - and then ramble on for pages, dodging and weaving, back-tracking, and wriggling to turn it into "incomplete". Laughable.Sknox put it better in the post previous to your first one, "This explanation feels a little light. For completeness, it should include the behavior of the LEFT() function as well."I don't know what you think your input added to that.[quote]I, and probably most civilised people regard that sort of deliberate selection of part of a statement to give the impression that something was said that clearly was not said[/quote]I have tried to be fair and extract the salient points from your verbose replies. Not my fault if you can't express yourself clearly.For the record, I haven't changed my position one iota.

RE: Column length

L' Eomot Inversé — Sat, 10 Apr 2010 11:27:15 GMT

[quote][b]Paul White NZ (4/10/2010)[/b][hr][quote][b]Tom.Thomson (4/9/2010)[/b][hr]I wish you would pay some attention..blah...blah..blah..[/quote]No. You failed to answer correctly because you made an incorrect assumption, based on incomplete knowledge. Attempting to wriggle and back-track to somehow blame the explanation is very poor form.Your rambling and border-line incoherent response above completely fails to address the text you quoted: The explanation perfectly adequately explains why IsUnknown becomes IsUnk. Your whole difficulty arises from an incorrect inference to the meaning of the word 'length' in that explanation. Your view relies on length being applied to the [i]value[/i] of numdesc inside the check_expression, as opposed to the maximum length of the type returned by LEFT. No doubt you would prefer the following:"the IsNull() function will truncate the length of replacement_value to the typed length of check_expression."[/quote] yes, that would be an improvement.[quote]And that is precisely the distinction that I regard as pedantic, overly-academic, picky, and ultimately tedious. This is a Question of the Day, not a paper - and your lack of flexibility does you no credit.[quote]I haven't changed what I'm saying. The explanation is incomplete.[/quote]You said it was wrong. Now you say it is incomplete. Back-tracking and wriggling.[/quote] More clever selective quoting. The line you quote a fragment of is actually[quote]The explanation is incomplete. What there is of it is also incorrect[/quote]I, and probably most civilised people regard that sort of deliberate selection of part of a statement to give the impression that something was said that clearly was not said as dishonest and disgraceful. I have said that it is incomplete and incorrect consistently throughout.[quote] [quote]...that does seem to be a bit of a retraction from wrong though, doesn't it[/quote]Yes. Makes it hard to keep track of what your current position is.[/quote]What? You are trying to say that when [b]you[/b] change from saying "wrong" to saying "pedantic" that's [b]me[/b] changing my position? That's pure drivel. Your position changed, not mind - I agree with neither your original "wrong" nor your later "pedantic".[quote]...I have good grounds to believe it is not documented...more thorough than you've undertaken, I suspect[/quote]It doesn't exist because you can't find it? :unsure:And you are claiming to be psychic now as well? I have not said that it definitely is documented - just that most behaviour is. Read back, and try to respond to what I actually said, rather than what you would like me to have said :-P[/quote] No I'm not psychic? Where did you pick up that nonsense from? The point you would do well to understand is that if you insist on saying "most behaviour is documented" as if it were an answer to the question whether this particular behaviour is documented you are being downright unhelpful.[quote] Let's be clear about this: I do not care why you got this question wrong. What I do object to is your over-use of strong words like 'nonsense', 'wrong', 'crazy', and 'arbitrary'. [/quote]And after this exchange, I don't care what you object to. [quote]It seems as if you think (or hope) others will not question your remarks if you present them as fact using sufficiently over-blown language. If you think it makes you look smart to be an arch-pedant over the wording of the explanation to a QotD...think again.[/quote]If you think it makes you look smart to use the tricks of selective quotation and attributing to people things they have no said that are normally associated with the depths of gutter journalism, then you need to think again.

RE: Column length

Paul White — Sat, 10 Apr 2010 01:23:03 GMT

[quote][b]Tom.Thomson (4/9/2010)[/b][hr]I wish you would pay some attention..blah...blah..blah..[/quote]No. You failed to answer correctly because you made an incorrect assumption, based on incomplete knowledge. Attempting to wriggle and back-track to somehow blame the explanation is very poor form.Your rambling and border-line incoherent response above completely fails to address the text you quoted: The explanation perfectly adequately explains why IsUnknown becomes IsUnk. Your whole difficulty arises from an incorrect inference to the meaning of the word 'length' in that explanation. Your view relies on length being applied to the [i]value[/i] of numdesc inside the check_expression, as opposed to the maximum length of the type returned by LEFT. No doubt you would prefer the following:"the IsNull() function will truncate the length of replacement_value to the typed length of check_expression."And that is precisely the distinction that I regard as pedantic, overly-academic, picky, and ultimately tedious. This is a Question of the Day, not a paper - and your lack of flexibility does you no credit.[quote]I haven't changed what I'm saying. The explanation is incomplete.[/quote]You said it was wrong. Now you say it is incomplete. Back-tracking and wriggling.[quote]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)?[/quote]No - whatever gives you that idea? That was your assertion in your first post: "The length of left(null,5) is not 5, it is null." - though it's not clear whether you mean the length of NULL is NULL (a tautology) or that the enclosing type would have a NULL length (which is wrong).[quote]...that does seem to be a bit of a retraction from wrong though, doesn't it[/quote]Yes. Makes it hard to keep track of what your current position is.[quote]...I have good grounds to believe it is not documented...more thorough than you've undertaken, I suspect[/quote]It doesn't exist because you can't find it? :unsure:And you are claiming to be psychic now as well? I have not said that it definitely is documented - just that most behaviour is. Read back, and try to respond to what I actually said, rather than what you would like me to have said :-PLet's be clear about this: I do not care why you got this question wrong. What I do object to is your over-use of strong words like 'nonsense', 'wrong', 'crazy', and 'arbitrary'. It seems as if you think (or hope) others will not question your remarks if you present them as fact using sufficiently over-blown language.If you think it makes you look smart to be an arch-pedant over the wording of the explanation to a QotD...think again.

RE: Column length

L' Eomot Inversé — Fri, 09 Apr 2010 23:01:23 GMT

[quote][b]Paul White NZ (4/9/2010)[/b][hr][quote][b]Tom.Thomson (4/9/2010)[/b][hr]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.[/quote]Good grief, man! :doze: [/quote]That exactly matches my reaction to your comments! :doze:[quote]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 [i]succinctly expresses[/i] 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 [i]your[/i] lack of understanding - not that of the questioner.[/quote]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.[quote] [quote]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.[/quote]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. [/quote]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). [quote][quote]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).[/quote]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:[/quote]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)? [quote]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 [i]defines[/i] its maximum length. [/quote] 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.[quote][quote]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.[/quote]I believe I have answered this already: The operator determines the type of the result, in all cases.[/quote] 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. [quote]Your refusal to check the documentation disqualifies you from making absolute statements about what is, and is not, documented. [i]You[/i] may find it surprising, [i]you[/i] may consider it inconsistent, [i]you[/i] may think it's wrong, nonsense or whatever. That doesn't make it so.[/quote]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?

RE: Column length

Paul White — Fri, 09 Apr 2010 21:42:51 GMT

[quote][b]Tom.Thomson (4/9/2010)[/b][hr]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.[/quote]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 [i]succinctly expresses[/i] 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 [i]your[/i] lack of understanding - not that of the questioner.[quote]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.[/quote]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.[quote]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).[/quote]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 [i]defines[/i] its maximum length.[quote]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.[/quote]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. [i]You[/i] may find it surprising, [i]you[/i] may consider it inconsistent, [i]you[/i] may think it's wrong, nonsense or whatever. That doesn't make it so.

RE: Column length

L' Eomot Inversé — Fri, 09 Apr 2010 10:26:04 GMT

[quote][b]Paul White NZ (4/9/2010)[/b][hr][quote][b]Tom.Thomson (4/9/2010)[/b][hr]I have to disagree. What you are telling me is that the length of the check expression is 5. That is pure nonsense.[/quote]The type of the check expression is VARCHAR(5) NULLable. It has a maximum length of 5. What is nonsensical or even [i]slightly[/i] hard to understand about that? [/quote]There is nothing nonsensical or hard to understand about what you are saying there. 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. Why do you want to claim it is?I'll skip most of the rest of your comment, because anything I said would be much the same as before, so pointless repetition: a waste of space; but on a couple of things it may be worthwile to say something.[quote] [quote]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.[/quote]I can only suggest you keep looking - most behaviour is documented in Books Online in detail. [/quote] 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. It's quite clear from its behaviour on a few test cases that it makes no use any type information available for its first argument other than to place an upper bound on the length component of the type of a NULL and to determine whether the character component is char or nchar, so what do I need documentation for? Oh, it might change in the next release if it's not documented - well, hard luck me).[quote] [quote]...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.[/quote]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.[/quote] [/quote]Clever piece of selective quoting there, sir, a nice "..." conceals the words that make nonsense of your remark about whining about the answer by stating clearly and unambiguously that the answer is correct. You know perfectly well that this discussion has nothing to do with the answer, but concerns the explanation. Why pretend it has?As to number of people, we first have abrar asking for more explanation (which is part of why I commented - and his reply to my comment suggests he found it useful) and sknox saying he thinks the explanation is inadequate and making much the same points as me in a looser style. So that's 3 people on one side. On the other side we have one liners from Jason and Ramchandra, a two-liner from yoursel (quibbling that the absence of a NOT NULL constraint was not explicitly declared - now that really is whining about nothing, isn't it) and a comment from dbowlin. That's 4 people. We don't have the usual collection of whiners claiming it's not fair they didn't get their point, because there is nothing anyone could hang such a claim on - as I said before, the question is clear and unambiguous and the answer is correct. So it's 3:4 - not a startling majority either way, is it? Or is that a grey area that you want to claim is white?My comments are fourfold: ([b]i[/b]) I chose the wrong answer (not because there's anything wrong with the question but because I didn't know that the type of the result of left(cast(null as varchar(50)),5) is varchar(5) - I should have known, but I didn't). ([b]ii[/b]) It's a good clear question with a good correct answer.([b]iii[/b]) 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). ([b]iv[/b]) 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. Do you actually disagree with any of these, or are you just having fun?

RE: Column length

Paul White — Fri, 09 Apr 2010 04:42:52 GMT

[quote][b]Tom.Thomson (4/9/2010)[/b][hr]I have to disagree. What you are telling me is that the length of the check expression is 5. That is pure nonsense.[/quote]The type of the check expression is VARCHAR(5) NULLable. It has a maximum length of 5. What is nonsensical or even [i]slightly[/i] hard to understand about that?[quote]The explanation completely fails to convey that, it says something completely different is happening. It does give a pointer to[b] part of[/b] the correct explanation.[/quote]No. It conveys the crucial point without being [i]verbose[/i].[quote]On that logic I could say what's wrong with * (or + or -) returning bigint when it needs to, instead of an error.[/quote]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.[quote]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.[/quote]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.[quote]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.[/quote]I can only suggest you keep looking - most behaviour is documented in Books Online in detail.[quote]...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.[/quote]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.

RE: Column length

L' Eomot Inversé — Fri, 09 Apr 2010 03:49:20 GMT

[quote][b]Paul White NZ (4/8/2010)[/b][hr]I see nothing wrong with the original explanation. It correctly conveys the reason that the result is 'IsUnk' and not 'IsUnknown'.[/quote]I have to disagree. What you are telling me is that the length of the check expression is 5. That is pure nonsense.[quote]The [i]check_expression[/i] 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.[/quote]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[b] part of[/b] the correct explanation.[[quote]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.[/quote]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 [url=http://msdn.microsoft.com/en-us/library/ms190286.aspx]Expressions (Transact-SQL)[/url]: [color="#0000FF"][i]When two expressions are combined by using arithmetic, bitwise, or string operators, the operator determines the resulting data type.[/i][/color][/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[code]declare @x int = 214735276, @y int = 1073741827select @x*@y [/code]doesn't return 230570247573589252 which on your logic it ought to return: what would baffle [b]you[/b] 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.

RE: Column length

Paul White — Thu, 08 Apr 2010 23:39:05 GMT

[quote][b]Tom.Thomson (4/8/2010)[/b][hr][quote]In SQL Server 2005 the IsNull() function will truncate the length of replacement_value to that of check_expression. [/quote]This explanation is wrong. The IsNull function converts to the type of the check_expression, which may involve truncation. It's nonsense to talk about truncating to the length of an expression whose value is null because that length is null. The length of left(null,5) is not 5, it is null.[/quote]I see nothing wrong with the original explanation. It correctly conveys the reason that the result is 'IsUnk' and not 'IsUnknown'.It has nothing to do with truncating NULLs. The [i]check_expression[/i] 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.From the BOL reference included with the explanation: [color="#0000FF"][i]"The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different."[/i][/color]The quick textual explanation is fine, especially so since it includes a BOL reference for further details. You are being overly picky here; this is a QotD, not an academic paper for peer review :-P[quote]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.[/quote]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 [url=http://msdn.microsoft.com/en-us/library/ms190286.aspx]Expressions (Transact-SQL)[/url]: [color="#0000FF"][i]When two expressions are combined by using arithmetic, bitwise, or string operators, the operator determines the resulting data type.[/i][/color]

RE: Column length

Abrar Ahmad_ — Thu, 08 Apr 2010 23:13:18 GMT

RE: Column length

L' Eomot Inversé — Thu, 08 Apr 2010 19:34:23 GMT

[quote]In SQL Server 2005 the IsNull() function will truncate the length of replacement_value to that of check_expression. [/quote]This explanation is wrong. The IsNull function converts to the type of the check_expression, which may involve truncation. It's nonsense to talk about truncating to the length of an expression whose value is null because that length is null. The length of left(null,5) is not 5, it is null.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.edit: spelling errors

RE: Column length

sknox — Tue, 06 Apr 2010 08:15:19 GMT

[quote]In SQL Server 2005 the IsNull() function will truncate the length of replacement_value to that of check_expression. [/quote]This explanation feels a little light. For completeness, it should include the behavior of the LEFT() function as well.Specifically, the truncation by ISNULL() is based on the datatype length not the data length (it would have to be, as NULL data would have 0 length.) This means that the LEFT() function is returning a shorter datatype than its source column (in this case a varchar(5)).I see no reason why this shouldn't happen this way, I just never thought about the datalength returned by substring functions before.

RE: Column length

Daniel Bowlin — Tue, 06 Apr 2010 07:05:16 GMT

Nice rework of a question a week or two ago. It is great to reinforce the information contained in these questions. I use these questions not so much as a test of my existing knowledge, but more as a reason to learn and dig into the questions and answers so I come away with more knowledge.Thanks,

RE: Column length

Paul White — Tue, 06 Apr 2010 04:56:06 GMT

Interesting variation on the normal ISNULL-related QotD.I do wish that the table definition had included an explicit NULL on the column definition though.

RE: Column length

p.ramchander — Tue, 06 Apr 2010 01:36:08 GMT

Nice Question.Thanks

RE: Column length

SQLRNNR — Tue, 06 Apr 2010 00:32:32 GMT

Nice question and useful information.

RE: Column length

Abrar Ahmad_ — Tue, 06 Apr 2010 00:19:25 GMT

Dear ken,Can you explain this behavior specifically w.r.t Operator Precedence? and is this behavior is default for all versions of SQL?Thank you!

Column length

ken.trock — Mon, 05 Apr 2010 21:30:50 GMT

Comments posted to this topic are about the item [B]Column length[/B]