Select Statement

  • sanjeewan99

    Say Hey Kid

    Points: 700

    Comments posted to this topic are about the item Select Statement

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Too easy 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • honza.mf

    SSCertifiable

    Points: 5519

    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.



    See, understand, learn, try, use efficient
    © Dr.Plch

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Robin Sasson

    Hall of Fame

    Points: 3359

    Good question - I use this example myself to teach those new to TSQL.

    :exclamation: "Be brave. Take risks. Nothing can substitute experience." :exclamation:

  • archie flockhart

    SSCrazy

    Points: 2339

    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.

  • Robin Sasson

    Hall of Fame

    Points: 3359

    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:

  • Iulian -207023

    SSCertifiable

    Points: 7509

    iep, easy one

    thanks,

    Iulian

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    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 decimal(38,10);

    SET @a = 1;

    SET @B = 10000000;

    SELECT @a / @B;

    go

    -- Example 2 - result is an error because the result won't fit in the assumed data type

    DECLARE @a decimal(38,10), @B decimal(38,10);

    SET @a = 1e+26;

    SET @B = 1e-6;

    SELECT @a / @B;

    -- 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


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • kapfundestanley

    SSCertifiable

    Points: 5632

    Thank you,an easy one.

    “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

  • Toreador

    SSChampion

    Points: 11257

    Good question. I suspect there'd have been fewer correct answers if "1" was an option!

  • Robin Sasson

    Hall of Fame

    Points: 3359

    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:

  • archie flockhart

    SSCrazy

    Points: 2339

    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 ?

  • This was removed by the editor as SPAM

  • jeff.mason

    SSCrazy Eights

    Points: 9555

    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 39 total)

You must be logged in to reply to this topic. Login to reply