May 21, 2012 at 10:54 pm
Comments posted to this topic are about the item Select Statement
May 21, 2012 at 11:14 pm
Too easy 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 22, 2012 at 12:53 am
Very easy.
But I think there is no type conversion. 1 is int, 2 is int, the type of both operands is same and result is int.
May 22, 2012 at 12:58 am
Good question (though a bit old - I think we've had lots of questions about this subject already). But not a good explanation.
There is no data type conversion. Both operands are the same data type (int - implicitly derived from the format used for the constants), so nothing has to be converted. The correct term that had to be used in the explanation is "integer division".
However, all that aside - thanks for contributing Sanjeewan, and I hope we'll see more questions from you.
May 22, 2012 at 2:02 am
Good question - I use this example myself to teach those new to TSQL.
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
May 22, 2012 at 2:04 am
I know this is how it does actually work, but does the behaviour not strike people as a bit strange ?
SQL Server is smart enough, when dividing two quantities that are Decimal(2,1), to know that it can't always fit the result into Decimal(2,1), so it allows a larger scale and precision for the answer. But not smart enough to know that when you divide two integers you can't always fit the answer into an integer ?
Incidentally, in the question, SELECT 2/3 might have been a bit trickier.
May 22, 2012 at 2:08 am
I think Smart would be nice but I prefer SQL Server not making smart assumptions!
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
May 22, 2012 at 2:25 am
iep, easy one
thanks,
Iulian
May 22, 2012 at 2:46 am
archie flockhart (5/22/2012)
SQL Server is smart enough, when dividing two quantities that are Decimal(2,1), to know that it can't always fit the result into Decimal(2,1), so it allows a larger scale and precision for the answer. But not smart enough to know that when you divide two integers you can't always fit the answer into an integer ?
It doesn't have anything to do with SQL Server being smart or not, but with predefined (and documented) behaviour. When dividing two data types of the same type, the resulting data type is always of that type, though precision and scale can change - see http://msdn.microsoft.com/en-us/library/ms190476.aspx. There is no guarantee that the result of non-integer division won't under- or overflow. For instance:
-- Example 1 - result is 0.000000 instead of 0.0000001
DECLARE @a decimal(38,10), @b-2 decimal(38,10);
SET @a = 1;
SET @b-2 = 10000000;
go
-- Example 2 - result is an error because the result won't fit in the assumed data type
DECLARE @a decimal(38,10), @b-2 decimal(38,10);
SET @a = 1e+26;
SET @b-2 = 1e-6;
-- Comment line above and uncomment line below for proof that result will fit in e.g decimal(38,5)
--SELECT CAST(CAST(@a AS float) / CAST(@b AS float) AS decimal(38,5));
go
May 22, 2012 at 2:46 am
Thank you,an easy one.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
May 22, 2012 at 2:49 am
Good question. I suspect there'd have been fewer correct answers if "1" was an option!
May 22, 2012 at 2:50 am
Thanks for the clarification Hugo - excellent as always. When I grow up I want to be like you.
:exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:
May 22, 2012 at 3:22 am
Hugo
You're right that as long as the behaviour is documented we can work with it - too late to change the behaviour now anyway ! - but a choice was made at some stage to build in the 'rule' that all arithmetic operations on two integers will return another integer.
Mathematically, that's true for addition, subtraction and multiplication - if we ignore the possibility of overflows which are a different issue. But it's not true for division: the set of integers is not closed under division. m/n where m and n are integers and n<>0 is pretty much the definition of Rational Numbers.
If it makes sense at all to divide one integer by another ( e.g. number of staff divided by number of offices) I think it makes sense to return fractional answers. It is probably much less common to divide two integers and want the answer to be the nearest integer rounded towards zero. But I'd be interested if people have different experiences of this and are actually using integer division without CASTing, and for what ?
May 22, 2012 at 5:36 am
This was removed by the editor as SPAM
May 22, 2012 at 7:19 am
Stewart "Arturius" Campbell (5/22/2012)
archie flockhart (5/22/2012)
If it makes sense at all to divide one integer by another ( e.g. number of staff divided by number of offices) I think it makes sense to return fractional answers. It is probably much less common to divide two integers and want the answer to be the nearest integer rounded towards zero. But I'd be interested if people have different experiences of this and are actually using integer division without CASTing, and for what ?Integer division is handled the same througout (albeit using Java, C#, C++, Cobol, Fortran, whatever). this an ANSI requirement.
to get the fractional part of Integer division, one uses the Modulo (x%y).
Yup, any Intro to Comp Sci class worth its weight will teach you that integer division and decimal division are two very different operations in computer science, and with integer divisions there are some very solid reasons why you don't want a fractional remainder (example -- you can use integer division and modulus to get the correct change on how many coins to return to a user). This isn't a SQL Server thing, this is a basic Computer Science thing, and SQL Server would need to carefully document and justify it if it were NOT like this.
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply