Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Column length Expand / Collapse
Author
Message
Posted Sunday, April 11, 2010 2:15 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:07 AM
Points: 8,745, Visits: 9,293
Paul White NZ (4/11/2010)
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!
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.
How do you know that? Why would I do that? What would be my motivation?
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.
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.
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.

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?

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?
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).
The person who had asked for more explanation appeared to like it. That matters more to my than all your venemous ranting

Well, that's rich! I would invite you to read your own submissions before casting aspersions.
I guess it's fair repayment for your remarks about wriggling etcetera? In any case, I never cast nasturtiums, only their seeds.

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

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?
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.

So you continue to distort.

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.
I suppose I have to accept that you tried to be fair if you say so; but I think you failed abysmally.

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.

No, Tom. You presume far too much. Your statement was not clear at all. I did not truncate it to misrepresent what I read.
As noted above, I find that pretty incomprehensible.

Then why did you say that your change from "wrong" to "pedantic" was a change in position?

I didn't - I thought you were referring to yourself in the following (complete) passage:

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.

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 - 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, 17th 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).

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.


Tom
Post #901292
Posted Monday, April 12, 2010 10:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 2,361, Visits: 2,702
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):
http://msdn.microsoft.com/en-us/library/ms184325%28SQL.90%29.aspx

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)

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/b221fb3e-c23e-49d5-b7ed-38ed7cab8238

Thanks,
webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #901755
Posted Monday, April 12, 2010 1:24 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:07 AM
Points: 8,745, Visits: 9,293
Oh dear . This makes me think maybe Paul was right when he said my post was unclear.

webrunner (4/12/2010)
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.

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.

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.
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.


Tom
Post #901900
Posted Monday, April 12, 2010 2:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 2,361, Visits: 2,702
Tom.Thomson (4/12/2010)
Oh dear . This makes me think maybe Paul was right when he said my post was unclear.


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.

webrunner (4/12/2010)
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.


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.


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().

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.
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.


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')

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.


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


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #901948
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse