I could not locate a valid explanation
And hyet, the explanation is simple. I lack the time to find the proper Books Online pages at this time, but they are in fact easy to find.
In five out of six batches, one operand is a character value and the other is an integer value. The rules of data type precedence specify (look up: "Data type precedence" in books online) that in such a case, the character operand (lower precedence) is converted to int (higher precedence) before the operator is invoked. The operator then operates on two integer values.
In the batch that errors, both operands are string. No data type conversion is required, and the operator is invoked. If the operator would have been +, that would have resulted in string concatenation. But the operator * is undefined for string operands. So, no bugs, no incorrect answer; SQL Server is operating is intended and as documented.
Overall a good question, but a disappointing explanation. Also, the subtle differences in the form of the batches made it harder to see what was going on. I almost jumped to the conclusion that all batches combined an int and a char value; the only reason that I continued to lkook deeper was that I figured that there had to be a catch somewhere. With u uniform format for all the questions, it would have been easier to spot the differences and focus on the skill the question indended to test.
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis