SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A round number


A round number

Author
Message
Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3583 Visits: 3889
Paul White NZ (5/4/2010)
Hugo Kornelis (5/4/2010)
Here is another repro that runs fine on all clients, to demonstrate that SQL Server itself actually does not have any issues with this code:
-- Or, the more direct approach
SELECT CAST(ROUND(0.5,0) AS decimal(12,2));


According to Jim Hogg (MSFT) on Connect, the error is raised by SQL Server, and is By Design:
https://connect.microsoft.com/SQLServer/feedback/details/364387/using-round-function-with-passing-numeric-expression-to-9-5-and-length-0

I don't think that this explanation is 100% correct, as we had already found out that the error is raised within the Client, not SQL Server (at least thats what I think)

This all still does not make too much sense to me, especially after you gave the hint regarding SQLCMD returning 0. Did not see that in the first place.

And thanks for your nice comment as well. Feels really good to get such positive feedback from real experts:-)

Best Regards,

Chris Büttner
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11018 Visits: 11994
Paul White NZ (5/4/2010)
Hugo Kornelis (5/4/2010)
Here is another repro that runs fine on all clients, to demonstrate that SQL Server itself actually does not have any issues with this code:
-- Or, the more direct approach
SELECT CAST(ROUND(0.5,0) AS decimal(12,2));


According to Jim Hogg (MSFT) on Connect, the error is raised by SQL Server, and is By Design:
https://connect.microsoft.com/SQLServer/feedback/details/364387/using-round-function-with-passing-numeric-expression-to-9-5-and-length-0

Good find, Paul.

But Jim is wrong. If his explanation was correct, than
DECLARE @x numeric(2,1); SET @x = ROUND(9.5,0);

should return an error, on all clients. It does not. But
DECLARE @x numeric(2,1); SET @x = ROUND(9.5,0); SELECT @x;

does return an error, on some clients.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
webrunner
webrunner
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4153 Visits: 3886
Interesting question, and fascinating discussion. At this point, I'm not sure what the final correct explanation will be.

If it turns out that the error is only in some clients, does that mean that those of us who got the question wrong by picking 1 can get ROUND(0.5,0) points for it? :-P - just kidding!

- webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Michael Poppers
Michael Poppers
SSC Eights!
SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)

Group: General Forum Members
Points: 829 Visits: 416
webrunner (5/4/2010)
Interesting question, and fascinating discussion. At this point, I'm not sure what the final correct explanation will be.

If it turns out that the error is only in some clients, does that mean that those of us who got the question wrong by picking 1 can get ROUND(0.5,0) points for it? :-P - just kidding!

- webrunner

I appreciate the discussion, but the question was ambiguous in the extreme, and I join those who didn't consider it a good QotD per se. Where I come from (the UpOver land of SimpleDom :-)), SELECT ROUND(0.5, 0) returns the sum of (a) the rounded-up value of the first operand and (b) a zapped fractional component in a value formatted the same as the first operand (in this case, NUMERIC(2,1)) -- again, I appreciate knowing that different query engines return different results, but that fact means that the QotD was not well-phrased! Just my tuppence.
Carla Wilson-484785
Carla Wilson-484785
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1917 Visits: 1950
Good question, great discussion. Very interesting problem. I definitely learned a lot today, including to watch how I set up ad hoc queries.
Thanks!
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15728 Visits: 11355
webrunner (5/4/2010)
If it turns out that the error is only in some clients, does that mean that those of us who got the question wrong by picking 1 can get ROUND(0.5,0) points for it?

Superb. Well done, sir! Laugh

It definitely is client-specific, but also server-specific w00t

Results so far for SELECT ROUND(0.5, 0) for me:

osql client to 2005 server = 1.0 (correct!)
SQLCMD client to 2005 server = .0 (wrong!)
SSMS 2005 to 2005 server = An error occurred while executing batch. Error message is: Arithmetic Overflow.
SSMS 2008 to 2005 server = An error occurred while executing batch. Error message is: Arithmetic Overflow.

osql client to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.
SQLCMD client to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.
SSMS 2008 to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.

Results so far for PRINT ROUND(0.5, 0) for me:
osql to 2005: 1.0 (correct!)
SQLCMD to 2005: 1.0 (correct!)
SSMS 2005 to 2005 server: 1.0 (correct!)
SSMS 2008 to 2005 server: 1.0 (correct!)

osql client to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.
SQLCMD client to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.
SSMS 2008 to 2008 server = Error 8115: Arithmetic overflow error converting expression to data type numeric.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62699 Visits: 19111
It's an interesting question. Good to see there's a Connect item on it, and people should have learned things.

Not sure how I feel about the question. Definitely if the server handles it, the client should.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Paul White
Paul White
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15728 Visits: 11355
Steve Jones - Editor (5/4/2010)
It's an interesting question. Good to see there's a Connect item on it, and people should have learned things.

I have..for one :-)

Not sure how I feel about the question. Definitely if the server handles it, the client should.

I think there's another lesson here - patch your client tools as well as the server.
I wonder how many people will be surprised by the client tools version returned by Help...About in SSMS.

The situation with SQL Server 2005 is very odd.
The server seems happy to process SELECT ROUND(0.5, 0) but assigns invalid metadata:

SELECT a = ROUND(0.5,0) INTO #a;
SELECT a FROM #a;


The first statement succeeds, but the data type of column a in #a is numeric(1,1)! w00t

DBCC CHECKDB on tempdb returns:
Msg 2570, Level 16, State 3, Line 1
Page (1:2373), slot 0 in object ID 258099960, index ID 0, partition ID 72057594068271104, alloc unit ID 72057594073382912
(type "In-row data"). Column "a" value is out of range for data type "numeric".
Update column to a legal value.




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32405 Visits: 18556
Wow...

Who would have thought the QOD would have unveiled a bug and produced great discussion. Congrats to all who have contributed to this discussion!!



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

mark.pleasance
mark.pleasance
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1628 Visits: 258
from within the Query Analyzer (SQL 2000 SP4) I got the resultant 1.0 as well.

just my $0.02.

M

Free Expert Advice . . .
http://xkcd.com/627/

Mark
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search