August 3, 2010 at 8:21 am
mtillman-921105 (8/3/2010)
I think that this "feature" should be changed. Just because two numbers are integers, why assume the answer should also be an integer? :angry:
I would like to disagree with this. The "feature" happens to be default, consistent behaviour in many programming languages. Try this is C#:
int i = 5, j = 3;
Console.WriteLine(i / j);
The above happily prints 1 to the console window. Why would anyone think that T-SQL should behave differently?
Very good question Hugo, thank you. It does not happen too often to be able to answer your QotD right off the bat without thinking much. 🙂
Oleg
August 3, 2010 at 10:54 am
Oleg Netchaev (8/3/2010)
mtillman-921105 (8/3/2010)
I think that this "feature" should be changed. Just because two numbers are integers, why assume the answer should also be an integer? :angry:I would like to disagree with this. The "feature" happens to be default, consistent behaviour in many programming languages. Try this is C#:
int i = 5, j = 3;Console.WriteLine(i / j);
The above happily prints 1 to the console window. Why would anyone think that T-SQL should behave differently?
<<snip>>
Oleg
The difference, Oleg, is that the 1st argument to ROUND() is a NUMERIC. I would nevertheless not want T-SQL's behavior to change :-).
August 3, 2010 at 10:58 am
Nice question thanks! (It is nice to know that the issues I have run into in the past have taught me some things, and that I still remember them.)
August 3, 2010 at 11:17 am
Michael Poppers (8/3/2010)
The difference, Oleg, is that the 1st argument to ROUND() is a NUMERIC. I would nevertheless not want T-SQL's behavior to change :-).
This is true, but who said that int is not numeric? Well, I will actually switch from numeric wordy to decimal at this point. I understand that they are synonyms and numeric is ANSI standard, but it just smells like Oracle to me, so I prefer decimal instead. Int can be considered as decimal(10, 0). From BOL:
For example, int data type can hold 10 digits, is stored in 4 bytes, and does not accept decimal points. The int data type has a precision of 10, a length of 4, and a scale of 0.
So, both statements below:
select round(5, 1);
select round(cast(5 as decimal(10,0)), 1);
are pretty similar, both print 5 in the results window. 🙂
Oleg
August 3, 2010 at 11:27 am
Oleg Netchaev (8/3/2010)
mtillman-921105 (8/3/2010)
I think that this "feature" should be changed. Just because two numbers are integers, why assume the answer should also be an integer? :angry:I would like to disagree with this. The "feature" happens to be default, consistent behaviour in many programming languages. Try this is C#:
int i = 5, j = 3;Console.WriteLine(i / j);
The above happily prints 1 to the console window. Why would anyone think that T-SQL should behave differently?
Very good question Hugo, thank you. It does not happen too often to be able to answer your QotD right off the bat without thinking much. 🙂
Oleg
Oleg, first, my standard is my calculator and if I put in 5/3, it shows 1.66666666.
Second, logically, 5 / 3 <> 1. It is actually closer to 2. So SQL is returning a false statement.
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.
However, I can accept your line of reasoning, but I still don't agree.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
August 3, 2010 at 12:13 pm
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-2 smallint;
select @i = 32767, @j-2 = 2;
print @i * @j-2;
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
August 3, 2010 at 2:52 pm
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! 🙂
August 3, 2010 at 2:57 pm
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
August 3, 2010 at 3:03 pm
nice question. Thanks for the explanation.
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 4, 2010 at 6:32 am
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;-)
August 4, 2010 at 6:40 am
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
August 4, 2010 at 11:50 am
Good Question. Thank you
August 14, 2010 at 3:21 am
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
April 22, 2011 at 11:53 am
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. :blush:
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply