August 24, 2010 at 6:45 am
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 WeirdColINTO WeirdTable;
go
EXEC sp_help 'WeirdTable';
go
UPDATE WeirdTable
SET WeirdCol = WeirdCol - 0.001;
go
DROP TABLE WeirdTable;
go
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?
Regards,
Hrvoje Piasevoli
Hi Hrovje,
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
August 24, 2010 at 6:50 am
Nope, just tested it on a SQL 2008 Express install and it came up with an arithmetic overflow as expected.
August 24, 2010 at 7:05 am
paul.knibbs (8/24/2010)
Nope, just tested it on a SQL 2008 Express install and it came up with an arithmetic overflow as expected.
Super! Thanks for testing and reporting back here.
Obviously, this weird behaviour that appears to be exclusive to the ROUND() function has been fixed in SQL Server 2008.
August 24, 2010 at 7:07 am
Interesting question....it was the -1 that made me stop and think for a minute
August 24, 2010 at 7:14 am
Hugo Kornelis (8/24/2010)
Super! Thanks for testing and reporting back here.
Obviously, this weird behaviour that appears to be exclusive to the ROUND() function has been fixed in SQL Server 2008.
Is it a problem with the ROUND() function, or something else? It seems odd that when you select the content of WeirdTable before dropping it that you get the single 1000.00 value returned on SQL 2005--you would have thought it wouldn't allow you to insert that value in a DECIMAL(5,2) column in the first place, regardless of where it came from!
August 24, 2010 at 7:25 am
paul.knibbs (8/24/2010)
Hugo Kornelis (8/24/2010)
Super! Thanks for testing and reporting back here.
Obviously, this weird behaviour that appears to be exclusive to the ROUND() function has been fixed in SQL Server 2008.
Is it a problem with the ROUND() function, or something else? It seems odd that when you select the content of WeirdTable before dropping it that you get the single 1000.00 value returned on SQL 2005--you would have thought it wouldn't allow you to insert that value in a DECIMAL(5,2) column in the first place, regardless of where it came from!
I think it's the ROUND() function, as I have so far not been able to reproduce this behavour in any other way.
Returning 1000.00 when you SELECT from the table is just as subject to the client being used as getting a result from running "SELECT ROUND(789.87, -3);" directly. QA and osql.exe display the value; SSMS returns an error message.
August 24, 2010 at 8:08 am
hrvoje.piasevoli (8/23/2010)
Hi great qod!Here's a tip:
Move the decimal point to the left by the negative number and then do a regular ROUND(d, 0). Applied to this it ends looking like ROUND(0.1666666,0) and there you have it.
I wish I'd come up with it before I answered incorrectly 🙂
Regards,
Hrvoje Piasevoli
Thanks for the tip. It would have kept me from going 0 for 4 on these rounding questions. Sigh.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
August 24, 2010 at 8:40 am
paul.knibbs (8/24/2010)
Didn't know ROUND() could do this, but once I looked it up in Books Online it seemed fairly straightforward--I learned something today, which is always nice!
Get ready for a future QotD involving the opt. 3rd param. to ROUND() (oops, I gave it away :-)).
August 24, 2010 at 8:50 am
Michael Poppers (8/24/2010)
paul.knibbs (8/24/2010)
Didn't know ROUND() could do this, but once I looked it up in Books Online it seemed fairly straightforward--I learned something today, which is always nice!Get ready for a future QotD involving the opt. 3rd param. to ROUND() (oops, I gave it away :-)).
Nope, I already did that last week! http://www.sqlservercentral.com/questions/T-SQL/70325/[/url]
The good news for Michael is that this was the last question I submitted about ROUND().
The bad news is that I still have two other questions scheduled for the next two tuesdays.:-P
August 24, 2010 at 9:00 am
Hugo Kornelis (8/24/2010)
Nope, I already did that last week! http://www.sqlservercentral.com/questions/T-SQL/70325/[/url]
Sorry -- I was away from computers last week and missed it (but I guess I can now look at it :-)).
The good news for Michael is that this was the last question I submitted about ROUND().
Thanks for helping us learn more about it!
The bad news is that I still have two other questions scheduled for the next two tuesdays.:-P
Keep up the good work!
August 24, 2010 at 9:21 am
Hi all, played for a while with Hugo's example and ... look what I found :w00t:
First, I have tested and it errors on 2008, works on 2005.
So, the following applies to SQL Server 2008. Here is the script, note the results from selects in the end of the script:
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Weird') BEGIN
ALTER DATABASE [Weird] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [Weird]
END
GO
CREATE DATABASE [Weird]
GO
-- set compatibility level
ALTER DATABASE [Weird] SET COMPATIBILITY_LEVEL = 90
GO
ALTER DATABASE [Weird] SET RECOVERY SIMPLE
GO
USE weird
go
SELECT ROUND(789.98,-3) AS WeirdCol
INTO WeirdTable;
-- 1 ok
SELECT COUNT(*) FROM WeirdTable
-- 2 doesn't work
SELECT WeirdCol FROM WeirdTable
--3 explicit cast works !!!
SELECT CAST(WeirdCol as decimal(6,2)) FROM WeirdTable
Now you have to admit that this is realy weird:hehe:
PS: If I had a blog this would probably be worth blogging about. Hugo?
Regards,
Hrvoje Piasevoli
Hrvoje Piasevoli
August 24, 2010 at 11:25 am
hrvoje.piasevoli (8/24/2010)
Hi all, played for a while with Hugo's example and ... look what I found :w00t:
Thanks for confirming that this is indeed apparently fixed in SQL 2008.
-- 2 doesn't workSELECT WeirdCol FROM WeirdTable
I'm willing to bet that this will work when run from Query Analyzer or osql.exe
PS: If I had a blog this would probably be worth blogging about. Hugo?
You can always start a blog! 😉
August 24, 2010 at 1:01 pm
Thanks for another great question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 24, 2010 at 10:53 pm
Hugo Kornelis (8/24/2010)
paul.knibbs (8/24/2010)
Nope, just tested it on a SQL 2008 Express install and it came up with an arithmetic overflow as expected.Super! Thanks for testing and reporting back here.
Obviously, this weird behaviour that appears to be exclusive to the ROUND() function has been fixed in SQL Server 2008.
i m still getting the error in sql server 2008... :unsure:
compatibility level - 100...
Microsoft SQL Server 2008 (SP1) - 10.0.2723.0 (X64) Enterprise Edition (64-bit)
August 25, 2010 at 6:01 pm
Thanks for the question! (Actually for the series of questions, I like the idea of going through lots of different uses of the same function.)
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply