The question is not bad (though I think a bit pointless; I hope people will never use this kind of data type mixing and complex ways to do simple calculations in real code). But the explanation is incorrect. It keeps mentioning reals, even where they are not involved. And some of the responses suffer from similar problems.
SO, let's look at this one step at a time:
select @Quota1 = @Value * 1.05;
Here, @Value is integer, and 1.05 is a constant. SQL Server will type this constant as decimal (not float!!) with the minimum required precision and length: decimal(3,2).
Since SQL Server can't multiply mixed data type, it uses the rules of data type precedence (http://msdn.microsoft.com/en-us/library/ms190309.aspx) to determine the required implicit conversions, and it will convert the integer value to decimal. To decimal(10,0) to be precise (because the largest int value has 10 digits).
Then, the multiplication is carried out. Rules for precision, scale, and length of operations (http://msdn.microsoft.com/en-us/library/ms190476.aspx) are applied, so that the result is now decimal(14,2), with a value of 21.00.
For assigning this to the integer @Quota1, it is truncated to 21.
select @Quota2 = cast(@Value as real) * 1.05;
The CAST is done first, so that the first operand (the integer value 20) is converted to real. 1.05 is still decimal(3,2), but now rules of data type precedence say that 1.05 will be converted to real. The calculation is done, the result is then truncated to integer. Since real uses approximate numbers, the result before truncation can be anything between 21 minus tiny fraction to 21 plus a tiny fraction - so after truncating, it is either 20 or 21. In this case, the value just happens to be 21.0000000000whatever, so we are lucky and get 21.
This ambiguity could have easily been avoided by using the ROUND function - that will always return the expected value.
select @Quota2 = ROUND(cast(@Value as real) * 1.05, 0);
Set @Value = 100;
select @Quota3 = cast(@Value as real) * 1.05;
Same as before - 100 is converted to float, 1.05 is converted to float, they are multiplied, and the result is somewhere very near to 105, which is then truncated to int. And in this case, the internal float results just happens to be 104.999999999whatever, so it's truncated to 104.
Again, rounding would have avoided the issue.
I guess the bottom line of this QotD is that if you mess up your code by mixing dat types and not handling the conversions appropriately, you'll get messes up results.
the way to handle this is to use round, which means going via numeric.
Correction - you don't have to go via numeric; ROUND will gladly accept float data as its input.
Lets play a bit:
Select cast(100 as real) * 1.05,cast(cast(100 as real) * 1.05 as int);
-- Result is 105, 104
The result is very strange for me. Does SQL Server the calculation from inside to outside or not? :w00t:
My problem is, that the first expression evaluates to 105, and if i cast the 105 to int it should remain 105 and not less.
Both functions first calculate a real result. That result is not exactly 105, but terribly close. The first one stops there (so it is returned as real to SSMS, which then does some rounding before displaying it - that's why you see 105, instead of 104.999999999whatever), and the second one goes on to cast as integer, which truncates, then sends that integer value to SSMS.