tommyh (8/24/2010)
Hugo Kornelis (8/24/2010)
ziangij (8/23/2010)
tommyh (8/24/2010)
With every setting at the default however SQL allows this. Now i havent been able to replicate the error.Frankly, this surprises me a lot more than the error ziangij gets. (And that I can replicate). As stated, I do get the same error message. (SQL 2005, SP3). What version did you test this on?
8.00.760SP3Standard Edition
9.00.1399.06RTMDeveloper Edition
Though i dont think it matters. Since 99.9% off all the code i write is for SQL2000 (yeah old). I use the old Query Analyzer. If i try the same in SSMS (2005) i get the error (with same options set in both programs). So not a SQL version issue but an issue with the program issuing the commands it seams.
I don't have SA, but I tried using osql.exe and I also got 1000.00 as the result.
I consider this a bug. The error you get in SSMS is actually the correct and expected behaviour. But somehow, for some weird reason, the evaluation of ROUND(748.58, -3) itself does not result in an error; instead the value of 1000.00 is "somehow" (don't ask me how!) encoded in the space available for a decimal(5,2). This is error number one.
Error number two is client-specific. Apparently, QA and osql.exe will happily display a value if the server tells them to display the value 1000.00 with three positions before and two positions after the decimal place. SSMS behaves better - it knows this is impossible, and tells us about that.
Here is an interesting experiment (works on every client):
SELECT ROUND(789.98,-3) AS WeirdCol
INTO WeirdTable;
go
EXEC sp_help 'WeirdTable';
go
UPDATE WeirdTable
SET WeirdCol = WeirdCol - 0.001;
go
DROP TABLE WeirdTable;
go