Comments posted to this topic are about the item A Round Number[/url]
Some extra detail on this question to anyone interested:
SQL Server uses internal rules to assign a type to a literal value. When SQL Server sees the 0.5 value it assigns a type to the expression according to its internal rules. A lot of the documentation speaks of SQL Server using the smallest type it needs to contain the value - so in this case a NUMERIC(1,1) would be used. That may or may not be the case on every version of 2005 and 2008 ever - but I think it covers the currently supported versions. The problem highlighted by this question is that the result of the ROUND does not fit in a NUMERIC(1,1) so an overflow error results.
According to all but the most recent Books Online entry (which seems apply only to 2008 R2) the ROUND function returns an expression with the same type as its first parameter: http://msdn.microsoft.com/en-us/library/ms175003(SQL.90).aspx. My version of 2008 BOL shows the same thing (dated October 2009).
However, the current online entry in Books Online says that ROUND returns DECIMAL(38, s) when presented with a numeric input with precision p and scale s: http://msdn.microsoft.com/en-us/library/ms175003.aspx.
That would be fine if it were so, and would avoid the problem highlighted by this question, but even the latest versions of 2008 (10.02766) and 2005 (9.04285) do not behave this way. I believe the documentation is referring to 2008 R2, which is not very helpful for the vast majority of us. This seems like a welcome change - though there may be unexpected side-effects in some code.
I'm hoping for a good discussion about this question because the documentation is so thin/poor in this area.
Finally, a snippet of code to explore the auto-type-assignment behaviour of your version of SQL Server:
-- Just declare a table variable with the constant value of interest as a computed column
-- The computed column has a GUID name to make it easy to find
DECLARE @a TABLE (dummy INT NULL, [AB85729E-F946-4165-9F71-9162A5CFCCEB] AS 0.5);
-- Show the type assigned to the column
SELECT table_name = (SELECT T.name FROM tempdb.sys.tables T WHERE T.[object_id] = C.[object_id]),
[type_name] = (SELECT Y.name FROM tempdb.sys.types Y WHERE Y.system_type_id = C.system_type_id),
max_byte_length = C.max_length
FROM tempdb.sys.columns C
WHERE C.name = N'AB85729E-F946-4165-9F71-9162A5CFCCEB';