Post reply

RAND Unions

  • September 28, 2014 at 12:43 am

    #73576

    Comments posted to this topic are about the item RAND Unions

  • September 28, 2014 at 12:44 am

    #1748650

    Good eazy one, thanx 😎

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • September 28, 2014 at 8:09 am

    #1748666

    Good question.

    Just like the last question on Random, I think the explanation could be improved.

    First, the misleading "for a connection" is there again. It shouldn't be. What RAND returns for a given seed is fixed independent of teh connection.

    The second is that the explanation doesn't explan why anything but the first coulum is the same in each of the three components of the union. The reason is that a RAND call with an explicit non-null seed determines the sequence of values generated by that RAND and all subsequent RAND without an explicit seed parameter in the same connection before the next RAND call with an explicit non-null seed parameter.

    RAND calls with an explicit NULL seed parameter aren't relevant to todays question, but in case anyone wonders what there effect is it's quite simple: deliver NULL as result but they don't affect the sequence for calls with no explicit parameter. For example the only difference in the values delivered by the two call sequences

    RAND(100), RAND(), RAND() and

    RAND(100), RAND(), RAND(NULL), RAND()

    is that the second sequence has a NULL in third position, in between the 2nd and 3rd values produced by the first sequence.

    Tom

  • September 28, 2014 at 10:41 am

    #1748674

    Thanks, added more detail to the explanation.

  • September 28, 2014 at 4:44 pm

    #1748689

    Steve Jones - SSC Editor (9/28/2014)

    Thanks, added more detail to the explanation.

    Ouch! That's wrong - not at all what I meant to say, and worse than the original. All calls after the first don't deliver the same value: don't say "all calls in the same connection return the same value until a new call with a non-null parameter" but something like "a call with a seed parameter defines not just one value, but a sequence of values so that the following sequence of calls returns the same sequence of values apart from any with null seed and result until a new call with a non-null parameter".

    I guess my comment should have been phrased better than it was.

    Tom

  • September 28, 2014 at 7:16 pm

    #1748698

    Sorry, I was in the middle of other stuff an paraphrased.

    I've removed some of that info to keep it simpler, since I can't really build a solid explanation to cover everything right now.

  • September 28, 2014 at 11:14 pm

    #1748702

    Good explanation. thanks allot.

    Thanks,
    Shiva N
    Database Consultant

  • September 28, 2014 at 11:42 pm

    #1748704

    Great question! Forces one to read the documentation before answering. Trick to the answer really lies in both - the behaviour of the RAND() function and the UNION operator.

    Thanks for the QotD!

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com

    Follow me on
    Twitter: @sqltwins

  • September 29, 2014 at 12:32 am

    #1748707

    there will be three rows as i have experianced. due to union ,

    because union takes all records .

  • September 29, 2014 at 1:02 am

    #1748710

    Great question Steve, thanks.

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

  • September 29, 2014 at 1:02 am

    #1748711

    gautamcs22 (9/29/2014)

    there will be three rows as i have experianced. due to union ,

    because union takes all records .

    No, that is "union all".

    Union removes distinct rows.

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

  • September 29, 2014 at 1:06 am

    #1748713

    Thanks Steve for this question, it highlights the issue with RAND documentation, which ironically is more random in Books Online than the actual output of the function itself. Or as the locals would say: "that's a bit random"

    😎

  • September 29, 2014 at 5:24 am

    #1748765

    gautamcs22 (9/29/2014)

    there will be three rows as i have experianced. due to union ,

    because union takes all records .

    The UNION also performs a DISTINCT on the result sets, so there is only one returned. Had a UNION ALL been used, you would get 3 rows back. I thought it was a good question.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • September 29, 2014 at 6:43 am

    This was removed by the editor as SPAM

  • September 29, 2014 at 7:24 am

    #1748805

    and what is the possibility if the each rand() return different result ?

Viewing 15 posts - 1 through 15 (of 30 total)

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