Fun with RAND()

  • Brian.Klinect (11/5/2014)


    I forgot RAND() was exclusive. So we can get .99999..., which multiplies to 9.999999..... I take it then that storing a floating point number as an integer just truncates in SQL?

    This may be one of those "it depends" SQL Server answers.

    Run this code and see if your results are the same as mine.

    Declare @decFloat Float = 9.99999999999999;

    Declare @intTest Integer = @decFloat;

    Select @decFloat As [Float], @intTest As [Integer];

    -- Returns: 9.999999999999999

    Set @decFloat = 9.9999999999999999999999999;

    Set @intTest = @decFloat;

    Select @decFloat As [Float], @intTest As [Integer];

    -- Returns: 109

    Set @decFloat = 9.999999999999999;

    Set @intTest = @decFloat;

    Select @decFloat As [Float], @intTest As [Integer];

    -- Returns: 1010It appears that sometimes we get rounding and sometimes truncating.

    Enjoy!

  • Do not like this question.

    There is no economy.

    What is actually used is the assignment to @intRND.

  • I also believe the answer is misleading. The select will return 1 row, 1 to 9 times, each one is a separate select of 1 row. A better question would ask how many times it would do a select, and how many rows were in each select, then we could determine the correct answer.

  • freeheel (11/5/2014)


    alvertorave (11/5/2014)


    I think the answer is wrong, you can only have 1 row, but you can execute the select 0 to 9 times returning a single row.

    my thought exactly. i got it right, but ...

    If you look at the question really critically it does not ask how many are selected. The question is "How many rows are returned?" The select is run 0-9 times, each select returns one row. So 0-9 rows are returned.

    Not all gray hairs are Dinosaurs!

  • Good question.

    I was careless, and ignored the implicit conversion of ((RAND() * 10) + 1) to int; since implicit conversion to int rounds towards 0 in SQL, and since RAND() delivers a positive value, that meant rounding down in this case but I just ignored rounding. So I picked the answer that would have been correct if @intRND had been declared as float instead of as int. I guess that was 4 glasses of red wine and a large brandy that went with lunch (I'm back in civilisation again, nowhere near England where that would be regarded as excess, and anyway it was my 70th birthday lunch). Without the alcohol I would probably have noticed that @intRND was a weird name for a float and might even have noticed that it was declared as an int :laugh:.

    Tom

  • Missed it due to carelessness. (Not noticing that it was < instead of <=). Well at least it taught me to pay more attention.

    Good question.

  • TomThomson (11/5/2014)


    Good question.

    I was careless, and ignored the implicit conversion of ((RAND() * 10) + 1) to int; since implicit conversion to int rounds towards 0 in SQL, and since RAND() delivers a positive value, that meant rounding down in this case but I just ignored rounding. So I picked the answer that would have been correct if @intRND had been declared as float instead of as int. I guess that was 4 glasses of red wine and a large brandy that went with lunch (I'm back in civilisation again, nowhere near England where that would be regarded as excess, and anyway it was my 70th birthday lunch). Without the alcohol I would probably have noticed that @intRND was a weird name for a float and might even have noticed that it was declared as an int :laugh:.

    Sounds like Tom's day was more fun than the question! (Which I liked very much.)

    Hope you had a great B-Day Tom.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Chiefly Confused (11/5/2014)


    I also believe the answer is misleading. The select will return 1 row, 1 to 9 times, each one is a separate select of 1 row. A better question would ask how many times it would do a select, and how many rows were in each select, then we could determine the correct answer.

    If the return from RAND() is less than .1, that value times ten will truncate to 0. Adding 1 will yield intRND = 1.

    Since intCounter starts at 1, the loop code won't execute. So, it could execute 0 to 9 times, not 1 to 9 times.

    Also, the question asks "How many rows are returned?" Didn't say for each SELECT, so very reasonable to assume that all SELECTS should be considered in answer. However, I agree that the question could have been written more clearly.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Dave62 (11/5/2014)


    Brian.Klinect (11/5/2014)


    I forgot RAND() was exclusive. So we can get .99999..., which multiplies to 9.999999..... I take it then that storing a floating point number as an integer just truncates in SQL?

    This may be one of those "it depends" SQL Server answers.

    Run this code and see if your results are the same as mine.

    Declare @decFloat Float = 9.99999999999999;

    Declare @intTest Integer = @decFloat;

    Select @decFloat As [Float], @intTest As [Integer];

    -- Returns: 9.999999999999999

    Set @decFloat = 9.9999999999999999999999999;

    Set @intTest = @decFloat;

    Select @decFloat As [Float], @intTest As [Integer];

    -- Returns: 109

    Set @decFloat = 9.999999999999999;

    Set @intTest = @decFloat;

    Select @decFloat As [Float], @intTest As [Integer];

    -- Returns: 1010It appears that sometimes we get rounding and sometimes truncating.

    Enjoy!

    Well, the float is rounding up in the last case to 10. The assignment to int is merely the same since it is a whole number sans decimal.

    In the case that the float does not round to the nearest whole number, then the assignment to int will round down to whole number that is less than the current float value. We can call that a truncate.

    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

  • results are different than the choices. in text mode its 9, in grid mode its 6.

    Thanks.

  • rabih_karam (11/5/2014)


    Smart question :-):-)

    thank you Dave

    rabih

    + 1

  • SQL-DBA-01 (11/5/2014)


    results are different than the choices. in text mode its 9, in grid mode its 6.

    Execute the query a bunch.;-) See what you get each time you execute it.

    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

  • Thank you for the post. I tried it on sql 2012 and got 7 rows

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Viewing 13 posts - 16 through 28 (of 28 total)

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