IMHO the only way this a good interview question is if the answer you're looking for is "I'm not sure of the correct answer because I would not rely on this implicit conversion. If I'm performing calculations I would explicitly cast / convert to the appropriate datatype."

Hugo, your explanation is excellent, thank you. I wouldn't have thought it was any of those answers; my first guess was 112. I forgot about type precedence in the implicit conversion and simply read left to right, so I figured ' ' + @a would convert to ' 10' and then adding the 2 would result in ' 102'. So I've got type precedence pretty well hammered into my head now, thanks!

Of course that's the secondary lesson. The primary lesson is never to allow code like this in your database.

While answering the question, I thought only of those cases (probably the vast majority) where I had used code like the code above (for concatenation in printing test values only, not for adding numbers), and in variably it included at least one non-numeric character. So I was biased to think that such code would always throw an error.

This question is a nice corrective for my ignorance in that regard.

Thanks,webrunner

-------------------"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

Thank you for an excellent explanation Hugo. The code like this should not be present anywhere near production of course, but the question is definitely a good one. One of the interesting side effects of implicit int to varchar conversions is that if the int value does not fit then varchar is set to *. For example, in the original example, if @a is set to 100 instead of 10 then it cannot fit into @b and this will cause @b = *, which can be somewhat confusing for someone who is unaware of this side effect. For example:

declare @a int;declare @b varchar(2);

set @a = 100;set @b = ' ' + @a + 2;

select @b;

The result is

----*

This behavior is specific to int to varchar conversions only, other types such as smallint or bigint will cause the code to raise error.

Hugo Kornelis (3/25/2010)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.

. . . . Order of precedence dictates conversion of varchar to int, and ' ' is converted to the value 0 . . . .

Thanks for the detailed explanation, Hugo. You beat me to it in pointing out that spaces (or an empty string) cast to numeric as value zero. A slight twist on the QOD code would dramatically illustrate that the leading spaces are not at all ignored:

Hugo Kornelis (3/25/2010)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.

. . . . Order of precedence dictates conversion of varchar to int, and ' ' is converted to the value 0 . . . .

Thanks for the detailed explanation, Hugo. You beat me to it in pointing out that spaces (or an empty string) cast to numeric as value zero. A slight twist on the QOD code would dramatically illustrate that the leading spaces are not at all ignored: