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 Monday, April 5, 2010 9:30 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 12:26 PM
Points: 323, Visits: 1,464
Comments posted to this topic are about the item Column length
Post #897287
Posted Tuesday, April 6, 2010 12:19 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:12 AM
Points: 212, Visits: 1,116
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
Posted Tuesday, April 6, 2010 12:32 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:20 PM
Points: 17,600, Visits: 15,462
Nice question and useful information.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


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
Posted Tuesday, April 6, 2010 1:36 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, February 18, 2013 5:19 AM
Points: 703, Visits: 172
Nice Question.

Thanks
Post #897367
Posted Tuesday, April 6, 2010 4:56 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:48 PM
Points: 9,923, Visits: 11,169
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
Posted Tuesday, April 6, 2010 7:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:35 AM
Points: 2,817, Visits: 2,563
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
Posted Tuesday, April 6, 2010 8:15 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:18 PM
Points: 1,325, Visits: 1,693
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
Posted Thursday, April 8, 2010 7:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 7,688, Visits: 9,410
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
Post #900098
Posted Thursday, April 8, 2010 11:13 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:12 AM
Points: 212, Visits: 1,116
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
Posted Thursday, April 8, 2010 11:39 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:48 PM
Points: 9,923, Visits: 11,169
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse