RAND Unions

Question

Post reply

RAND Unions

Steve Jones - SSC Editor

SSC Guru

Points: 736172
More actions
September 28, 2014 at 12:43 am

#73576

Comments posted to this topic are about the item RAND Unions
September 29, 2014 at 6:43 am

This was removed by the editor as SPAM

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

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

Hany Helmy SSChampion Points: 13921 More actions · Answer 1

Good eazy one, thanx 😎

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

TomThomson SSC Guru Points: 104773 More actions · Answer 2

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

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 3

Steve Jones - SSC Editor

SSC Guru

Points: 736172

September 28, 2014 at 10:41 am

#1748674

Thanks, added more detail to the explanation.

TomThomson SSC Guru Points: 104773 More actions · Answer 4

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

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 5

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.

Shiva N Hall of Fame Points: 3125 More actions · Answer 6

Good explanation. thanks allot.

Thanks,
Shiva N
Database Consultant

Nakul Vachhrajani SSChampion Points: 10294 More actions · Answer 7

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

gautamcs22 Grasshopper Points: 16 More actions · Answer 8

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

because union takes all records .

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 9

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 11

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"

😎

Ed Wagner SSC Guru Points: 287026 More actions · Answer 12

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

gautamcs22 Grasshopper Points: 16 More actions · Answer 13

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