Click here to monitor SSC
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
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2951 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
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8339 Visits: 11584
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
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3035 Visits: 3755
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
Right there with Babe
Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)Right there with Babe (761 reputation)

Group: General Forum Members
Points: 761 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.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1715 Visits: 1949
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10350 Visits: 11350
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-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36302 Visits: 18752
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10350 Visits: 11350
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-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21113 Visits: 18259
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: 1596 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