Random values

Question

Random values

Steve Jones - SSC Editor

SSC Guru

Points: 736222
More actions
September 24, 2014 at 9:56 pm

#75698

Comments posted to this topic are about the item Random values
September 25, 2014 at 3:17 am

This was removed by the editor as SPAM

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

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

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

Mmm, I just ran the code using SQL 2012 and I get 10 different result. Each of the records uses a different seed and, hence, should produce a different result.

The query is equivalent to something like

SELECT RAND(1) UNION SELECT RAND(2) .... UNION SELECT RAND(10).

So, I reckon that the answer of "1, every value is the same" is actually incorrect.

Andrew G SSChampion Points: 12809 More actions · Answer 2

SQL 2000: Error 'Row_Number' is not a recognized function name.

SQL 2005 to 2012: 10 distinct rows returned, answer should be C.

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

is it for single time execution or more than 1 time execution?

if 1 time it will give all distinct values, if more than once will give same as 1st result.

Thanks,
Shiva N
Database Consultant

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 4

Arrggg, wrong box checked.

10 is correct. I'll award back points tomorrow.

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

Andrew G (9/24/2014)
SQL 2000: Error 'Row_Number' is not a recognized function name.
SQL 2005 to 2012: 10 distinct rows returned, answer should be C.

you should never expect any of our questions to work on 2000. Some might, but we'll never test there.

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 6

Shiva you are right.

Good observation...:-)

Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

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

Great question Steve!

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

edwardwill SSCertifiable Points: 5851 More actions · Answer 8

Can you explain why you aliased the sys.syscolumns table?

SELECT TOP 10

RAND( ROW_NUMBER() OVER (ORDER BY id))

FROM sys.syscolumns

is functionally identical, as far as I am aware.

Hany Helmy SSChampion Points: 13921 More actions · Answer 9

Steve Jones - SSC Editor (9/24/2014)
Arrggg, wrong box checked.
10 is correct. I'll award back points tomorrow.

-1 errrrrr :angry:

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 10

Shiva N (9/24/2014)
is it for single time execution or more than 1 time execution?
if 1 time it will give all distinct values, if more than once will give same as 1st result.

Good catch.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

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

I thought that was a good question. It showed a different way of calling the random function. Thank you for it.

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

TomThomson SSC Guru Points: 104773 More actions · Answer 12

Good question, but two small flaws:

1: the explanation is misleading. It says "The RAND function only produces one value for all calls in a specific connection with a specific seed" which would tend to make people think that a given seed might produce different values in different connectipns. It doesn't: the for a given seed the value produced by RAND is alwys the same, regardless of what connection it is called in.

2: the values produced are not random, RAND with a seed is deterministic (as clearly stated in BOL Deterministic

and Nondeterministic Functions) - the value for a given seed is always the same.

Tom

SqlMel SSCrazy Points: 2901 More actions · Answer 13

Thanks for the question, Steve.

I think this is a fairly easy question. The only thing that got me thinking was whether or not it would throw an error.

---------------
Mel. 😎