L' Eomot Inversé (1/5/2012)
SQL Kiwi (1/4/2012)
As far as I recall, the issue with multiplying or dividing by one was a bug in type inference, which has since been fixed.Well, I'm on an XP platform so I can't test if it is fixed in Denali, but it certainly isn't fixed in SQL 2008 R2 with latest updates.
Maybe someone could try this in Denali:
Hm, I was confusing what I thought you were describing with this bug: https://connect.microsoft.com/SQLServer/feedback/details/557523/automatic-type-assignment-causes-data-corruption, which is 'fixed' (for some value of 'fixed'). Your code example still throws the same error in the latest build of Denali I have, with TF 4199 on just in case it was an optimizer change that requires that flag. I need to look at it a bit more closely to understand fully what the script is telling me - do you know if there is an existing bug report on Connect for this?
You are right that it's a type inference problem, of course - if you have types where the precision and scale (in the T-SQL sense; T-SQL makes awfully inaccurate use of both those terms, of course, so this is far from the normal sense) are part of the type definition and you also allow type inference to decide the type of a computed column in a table definition so that the definition doesn't have to specify that type you will have to have a mechanism for allowing a special type inference to decide precision and scale; that becomes a problem when you allow that same special mechanism to rule when the result of the computation is being assigned to a column or variable of known type, because overflow should be determined by whether the value resulting can be represented in that known type, not on whether some rather pointless kludge to avoid explicitly defining the type of a computed column when defining the table that contains it would implicitly produce a type that would allow the value to be represented.
You're probably right. I should say, in case I have given a different impression previously, that the type system in SQL Server is a bit of a mess, and the fear of making improvements that 'break' backward compatibility is a sad and distressing thing. There are all sorts of things in T-SQL that ought to be redone completely, perhaps even throwing T-SQL away completely, but that's a whole different discussion, and something that doesn't look any more likely to happen than SQL Server adding some new decimal type with better semantics and more logical behaviours in general.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi