SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What will be output?


What will be output?

Author
Message
Sunil Chandurkar
Sunil Chandurkar
Old Hand
Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)

Group: General Forum Members
Points: 377 Visits: 81
Comments posted to this topic are about the item What will be output?
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64085 Visits: 18570
Thanks for the Question.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

ziangij
ziangij
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3575 Visits: 377
thanks :-)... its a good interview question.
SRatnaparkhi
SRatnaparkhi
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 57
Thanks good question :-). For info rmation if we write question like this

declare @a int
declare @b varchar(2)
set @a = 10
set @b = ' '+@a +' '+ 2+' '
select @a +' '+ @b

then also output will be same.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18313 Visits: 12426
Good question, but unfortunately the explanation is not entirely correct. Especially this part is very misleading:
Also the preceding space is not considered while performing the operation.


Let's look in detail what happens to set @b = ' '+@a + 2. Since all operators are the same, evaluation order is left to right. So the first partial expression to evaluate is ' '+@a. This mixes two datatypes: ' ' is varchar(1), and @a is int. Order of precedence dictates conversion of varchar to int, and ' ' is converted to the value 0 (run SELECT CAST(' ' AS int); if you don't believe me). Since @a is set to 10, the result of this partial expression is 0 + 10, or 10; still types as int.

The next step is to evaluate (' '+@a) + 2 (parentheses added to emphasize evaluation order). We have already seen that (' '+@a) is equal to the integer value 10, so this is now reduced to 10 + 2.

As you have seen, there is no leading space to be considered anywhere. However, even if there was, the words "is not considered" is misleading. I think we would all agree that converting the values '5', ' 5' and ' 5 ' to integer should all result in the value 5 - not because we expect the leading and trailing spaces to be "not considered", but because considering them or not is irrelevant, as they have no impact on the interpretation of the string as a number.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18313 Visits: 12426
ziangij (3/24/2010)
thanks :-)... its a good interview question.
I would not be too happy with these things being asked in an interview. My answer would be that mixing data types like this and taking a dependency hit on implied evaluation order and implicit conversion rules should not be done in production code.

Code like this would get a red mark from me if I caught it in code review.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Bob JH Cullen
Bob JH Cullen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1098 Visits: 786
I would certainly agree with Hugo - this just smells like bad practice if it ends up in production code. OK, the question is fine as an academic exercise, but just because you can do something like this doesn't mean you should, IMHO.
btw - I got it wrong - probably because my style of coding doesn't give me enough practice at this kind of dubious T-SQL ;-)
vk-kirov
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4292 Visits: 4408
Hugo Kornelis (3/25/2010)
' ' is converted to the value 0 (run SELECT CAST(' ' AS int); if you don't believe me).

This is very interesting because ISNUMERIC(' ') returns 0.
On the one hand, SQL Server doesn't consider whitespaces as numeric values; on the other hand, it converts whitespaces to zero integer values. This is a little bit strange :-)
ganeshanmsc
ganeshanmsc
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 168
The answer is 22
Sunil Chandurkar
Sunil Chandurkar
Old Hand
Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)Old Hand (377 reputation)

Group: General Forum Members
Points: 377 Visits: 81
Thanks Hugo! your explaination is very helpful. also thanks for pointing out the misleading pharse. in future i can now be more careful for writing any stuff on SSC
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search