Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Round up or down I Expand / Collapse
Author
Message
Posted Tuesday, August 3, 2010 12:13 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 12:49 PM
Points: 1,676, Visits: 1,754
mtillman-921105 (8/3/2010)
However, I can accept your line of reasoning, but I still don't agree.

I can easily accept your line. I know, however, that it happens more often than not that we do have to agree with whatever implementation is, regardless whether it is consistent between different languages or not. If I try to use this in T-SQL:

declare @i smallint, @j smallint;
select @i = 32767, @j = 2;
print @i * @j;

I get "Arithmetic overflow error converting expression to data type smallint" error. When I try to use similar in C#:

short i = 32767;
i *= (short)2;
Console.WriteLine(i);


I get -2 printed in the Console window. I understand that neither of the above is what someone would perceive as reasonable, but I still accept both implementations. T-SQL does not allow overflows while C# simply wraps around (kinda like the sine) which is a great feature and it is used extensively in cryptography. So short 32767 + 2 is equal to -32767, and if I don't like it, I can force the T-SQL like behaviour by including the manipulation into checked {} block.

Oleg
Post #963038
Posted Tuesday, August 3, 2010 2:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 5,969, Visits: 8,224
mtillman-921105 (8/3/2010)
Third, us humans don't think this way typically I don't believe (thus, the good main question here). Since SQL is our tool, we should dictate how it works rather than vice-versa.

Which, for the record, can be done by explicitly casting the arguments to the desired data type. Which you'll only do if you know how the tool works.

Thanks for the interesting discussion, mtillman and Oleg!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #963128
Posted Tuesday, August 3, 2010 2:57 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, July 14, 2014 10:08 AM
Points: 754, Visits: 3,816
Hugo Kornelis (8/3/2010)
mtillman-921105 (8/3/2010)
Third, us humans don't think this way typically I don't believe (thus, the good main question here). Since SQL is our tool, we should dictate how it works rather than vice-versa.

Which, for the record, can be done by explicitly casting the arguments to the desired data type. Which you'll only do if you know how the tool works.

Thanks for the interesting discussion, mtillman and Oleg!



You're welcome Hugo. Thanks for the terrific question too!


______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
Post #963131
Posted Tuesday, August 3, 2010 3:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 21,631, Visits: 15,289
nice question. Thanks for the explanation.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #963136
Posted Wednesday, August 4, 2010 6:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
i loved this internal hidden casting in case of integer values

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #963422
Posted Wednesday, August 4, 2010 6:40 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Sunday, August 17, 2014 2:48 PM
Points: 3,352, Visits: 1,481
Completely missed what the question was asking until after I'd answered it. Spent far too long looking at the ROUND() part of it, and not the integer division part.

I should have seen it was worth 2 points and paid more attention. Or if the questions still showed who wrote them actually on the page (rather than just in the email, which I don't always look at) I could have seen it was by Hugo and stopped for a second before hitting submit.

Anyway, good question, it's important to be reminded of these things once in a while.

Duncan
Post #963433
Posted Wednesday, August 4, 2010 11:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 19, 2011 7:26 AM
Points: 1,078, Visits: 289
Good Question. Thank you
Post #963689
Posted Saturday, August 14, 2010 3:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:50 AM
Points: 2,649, Visits: 766
Ditto... good question. Sql rounding is confusing when you have been trained to add .5 to an integer to round - as it doesn't work that way. Questions like this are fascinating. Exposes the shell and opens it up to scrutiny.

Jamie
Post #969373
Posted Friday, April 22, 2011 11:53 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, March 4, 2012 4:02 AM
Points: 660, Visits: 134
This is a good question. Thanks.

It should have been easy to spot the conversion to Int, but I was more focussed on the 1 in the ROUND function.
Post #1097554
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse