Hugo Kornelis (9/12/2010)
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 aINTO 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?
)
that intrigued me, so I did some more documentation checking.
It appears that there was a change between SQLS 2005 and SQL S2008; the 2005 version of BoL (http://msdn.microsoft.com/en-us/library/ms175003(v=SQL.90).aspx) says that the result of ROUND has the same type as the expression to be rounded, as in your explanation. The 2008 version (http://msdn.microsoft.com/en-us/library/ms175003(v=SQL.100).aspx) and the current version (http://msdn.microsoft.com/en-us/library/ms175003.aspx) both say that for neumerics and decimals the result type is decimal with precision 38. So the explanation you gave was apparently correct for SQL 2005, and incorrect for SQL 2008.
I haven't been able to check the 2008 behavious as I don't yet have SQL on the machine I have with me and am having difficulty getting hold of SQL server at a sensible price. My usual UK suppliers are quoting more for shipping a disc here than the total for licence, disc, and shipping if I were in the UK, so the total price ends up about 2.5 times what it would have been if I'd fixed it last week before I flew out; so I'm looking for a local supplier and in parallel trying to persuade of of the usual suppliers to ship be airmail instead of using UPS or any of the other overpriced and unreliable delivery companies that are so much in vogue in the computing industry; of course if I were in the US I could just buy online and download, but MS doesn't give me that option here. The people I buy other stuff from [books, audio discs, dvd discs] ship to here cheaply using the international mail system instead of fashionable private delivery companies, so I foolishly expected the same from software vendors. I'll try to remember to run your test code when I get the license sorted and the software installed.
Tom