hrvoje.piasevoli (8/24/2010) Hugo Kornelis (8/24/2010)
Here is an interesting experiment (works on every client):
SELECT ROUND(789.98,-3) AS WeirdCol
EXEC sp_help 'WeirdTable';
SET WeirdCol = WeirdCol - 0.001;
DROP TABLE WeirdTable;
Hugo what is the expected result? On my system it's an empty table (nothing weird about the table though :cool
and the update operates on 0 rows - therefore nothing is really happening further. Are you referring to the fact that the table gets created despite the arithmetic overflow?
What version did you run this on? Maybe the problem has been fixed in SQL 2008?
On SQL Server 2005, the table gets created AND populated with a single row. In that row, the value 1000.00 is "somehow" stored in a numeric(5,2) column. The UPDATE then failes (because 1000.00 - 0.001 = 999.999; converted back to numeric(5,2) it rounds to 1000.00 again, and now it will not store the same value).
You can even get the update to fail by using SET WeirdValue = WeirdValue - 0.0
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis