<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by ken.trock
»
Column length
24 posts, Page 1 of 3
1
2
3
»
»»
Column length
Rate Topic
Display Mode
Topic Options
Author
Message
ken.trock
ken.trock
Posted Monday, April 05, 2010 9:30 PM
Old Hand
Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:39 AM
Points: 315,
Visits: 1,352
Comments posted to this topic are about the item
Column length
Post #897287
Abrar Ahmad_
Abrar Ahmad_
Posted Tuesday, April 06, 2010 12:19 AM
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 5:49 AM
Points: 202,
Visits: 1,043
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!
Post #897333
SQLRNNR
SQLRNNR
Posted Tuesday, April 06, 2010 12:32 AM
SSCoach
Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:46 PM
Points: 18,732,
Visits: 12,329
Nice question and useful information.
Jason
AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #897339
p.ramchander
p.ramchander
Posted Tuesday, April 06, 2010 1:36 AM
Say Hey Kid
Group: General Forum Members
Last Login: Monday, February 18, 2013 5:19 AM
Points: 703,
Visits: 172
Nice Question.
Thanks
Post #897367
Paul White
Paul White
Posted Tuesday, April 06, 2010 4:56 AM
SSChampion
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989,
Visits: 10,529
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.
Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #897434
Daniel Bowlin
Daniel Bowlin
Posted Tuesday, April 06, 2010 7:05 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 11:53 AM
Points: 2,672,
Visits: 2,416
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,
Post #897512
sknox
sknox
Posted Tuesday, April 06, 2010 8:15 AM
Ten Centuries
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:22 AM
Points: 1,037,
Visits: 1,354
In SQL Server 2005 the IsNull() function will truncate the length of replacement_value to that of check_expression.
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.
Post #897574
L' Eomot Inversé
L' Eomot Inversé
Posted Thursday, April 08, 2010 7:34 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 6:38 PM
Points: 7,077,
Visits: 7,116
In SQL Server 2005 the IsNull() function will truncate the length of replacement_value to that of check_expression.
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
Tom
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)
Post #900098
Abrar Ahmad_
Abrar Ahmad_
Posted Thursday, April 08, 2010 11:13 PM
SSC Veteran
Group: General Forum Members
Last Login: Yesterday @ 5:49 AM
Points: 202,
Visits: 1,043
Tom.Thomson (4/8/2010)
In SQL Server 2005 the IsNull() function will truncate the length of replacement_value to that of check_expression.
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
Yup, nice and more convincing.
Post #900193
Paul White
Paul White
Posted Thursday, April 08, 2010 11:39 PM
SSChampion
Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989,
Visits: 10,529
Tom.Thomson (4/8/2010)
In SQL Server 2005 the IsNull() function will truncate the length of replacement_value to that of check_expression.
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.
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
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.
From the BOL reference included with the explanation:
"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."
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
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.
Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #900198
« Prev Topic
|
Next Topic »
24 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
