Tom.Thomson (9/12/2010)
I think there's a small error in the explanation here. Round never returns a result of type decimal(8,6) because every decimal result that it returns has precision 38 - the datatype for decimal and numeric types is changed by round to decimal(38,s) where s is the scale of the original type. This is documented quite clearly in BoL (http://msdn.microsoft.com/en-us/library/ms175003.aspx). So it will return a value of type decimal(38,6) which is then implicitly converted to decimal(5,2) for the assignment.
Hi Tom,
Thanks for catching that. I can not believe I actually overlooked that (though the hyperlink is damaged, I did include a link to that article in the explanation).
However, I did not just post without checking. Here is how I checked my statement about the data type returned by the ROUND function:
SELECT ROUND(5/3.0, 1, 2) AS a
INTO b;
go
EXEC sp_help b;
go
DROP TABLE b;
go
On SQL Server 2005, the output of sp_help indicates that the column is typed as numeric(8,6), not numeric(38,6). I cannot check this on SQL Server 2008, as I don't have that version installed on this computer. Maybe you can execute this code on SQL 2008 and report back the results?
(Of course, for this particular question it does not really matter; there is no overflow so the numeric(38,6) would just add 30 extra leading zeroes and not affect the result - but now that you found this, I do want to go to the bottom of it;-))