Some more tests showing the bug "in action":
SELECT '0.5' [Expression], CAST(0.5 as varbinary) AS [Binary Representation]
UNION ALL SELECT 'ROUND(0.5,0)', CAST(ROUND(0.5,0) as varbinary)
UNION ALL SELECT '1.0', CAST(1.0 as varbinary)
UNION ALL SELECT '0.0', CAST(0.0 as varbinary)
Expression Binary Representation
The binary representation of the rounded value shows the problem: A (10) does not fit into decimal(1,1). This is definitively a bug (as already shown by Paul with DBCC CHECKDB)
Just some further explanation on the binary representation:
The first 2 digits seem to be the precision, the second 2 digits the scale.
I have no idea what the following 4 digits ('0001') mean
The rest seems to be the number without decimal point (in reverse byte order)
Precision = 2, Scale = 1, Value = A (10)
Since we have a scale of 1, the decimal point is inserted between the 1 and the 0:
So obviously the following is not a valid binary representation of the decimal datatype:
You cannot have a precision of 1 for the value A (10)
You either need a precision of 2 for that, or you need to have the value 1 instead.
Paul, did you already (re-)open a connect item, or should I do that?